Setup DB2
for sending and receiving SMS

In this chapter you can learn about how to send and receive SMS messages using a DB2 database server with your Ozeki Phone System XE. In this guide you can get information about the installation steps, the database connection string, the database connection type and the database table layout for DB2 database server.

In order to send or receive SMS messages using a DB2 database server, you need to following the instructions.

During the configuration you should choose the following database connection type:

OleDb

You should use the following database connection strings:

If you use a TCP/IP connection:

Provider=DB2OLEDB;Network Transport Library=TCPIP;Network Address=130.120.110.001;
Initial Catalog=MyCatalog;Package Collection=MyPackageCollection;Default Schema=MySchema;
User ID=MyUsername;Password=MyPassword;

If you use APPC connection:

Provider=DB2OLEDB;APPC Local LU Alias=MyLocalLUAlias;APPC Remote LU Alias=MyRemoteLUAlias;
Initial Catalog=MyCatalog;Package Collection=MyPackageCollection;Default Schema=MySchema;
User ID=MyUsername;Password=MyPassword;

The database connection string has to be modified to match your database.

The connection string and the connection type should be entered on the SQL SMS API configuration form.


Figure 5 - Specifying the connection string for the DB2 database

Figure 6 shows the SQL queries that will send the SMS messages from your database.


Figure 6 - SQL queries for receiving SMS messages

Under the SQL for receiving tab, you need to provide an SQL query that will store the incoming SMS messages. Figure 3 shows the SQL query that you need to use.


Figure 3 - SQL query for sending SMS messages

After the SQL SMS API has been configured in the Ozeki Phone System XE, you need to create the database tables for SQL SMS API. Use the following table layout: 

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozpbxmessageout`
-- -----------------------------------------------------
CREATE TABLE ozpbxmessageout
   (id integer NOT NULL GENERATED ALWAYS AS 
       IDENTITY (START WITH 1 INCREMENT BY 1),
    sender        VARCHAR(30)  NULL,
    receiver      VARCHAR(30)  NULL,
    msg           CLOB         NULL,
    senttime      VARCHAR(100) NULL,
    receivedtime  VARCHAR(100) NULL,
    reference     VARCHAR(100) NULL,
    status        VARCHAR(20)  NULL,
    msgtype       VARCHAR(160) NULL,
    operator      VARCHAR(100) NULL,
    errormsg      VARCHAR(250) NULL,
    PRIMARY KEY (id))

CREATE UNIQUE INDEX INDEX ozpbxmessageoutindex ON 
                    ozpbxmessageout (id)

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozpbxmessagein`
-- -----------------------------------------------------
CREATE TABLE ozpbxmessagein
   (id integer NOT NULL GENERATED ALWAYS AS 
       IDENTITY (START WITH 1 INCREMENT BY 1),
    sender        VARCHAR(30)  NULL,
    receiver      VARCHAR(30)  NULL,
    msg           CLOB         NULL,
    senttime      VARCHAR(100) NULL,
    receivedtime  VARCHAR(100) NULL,
    extension     VARCHAR(100) NULL,
    PRIMARY KEY (id))

CREATE UNIQUE INDEX INDEX ozpbxmessageinindex ON 
                    ozpbxmessagein (id)

It is strongly recommended to maintain an index in the database for the 'id' field of the outgoing SMS table to support faster SQL updates.

If you have any questions or need assistance, please contact us at info@ozekiphone.com.

Dig deeper!
People who read this also read...

More information