The following program is an example of CLI and embedded SQL statements in the same C program to perform array inserts to achieve high performance. Why do you need to use a mixture of CLI and embedded SQL program? Sometimes, it is desirable from the coding perspective as you use the power of CLI to use array inserts, import, export and load tables and use normal embedded SQL statements as usual.
For an example, if you want to perform an array insert using dynamic SQL statements in your embedded SQL program, you will have to copy all of your array variables to host variable and supply all those parameters through EXEC SQL EXECUTE dynstmt USING :hostvar1, :hostvar2 etc. etc. The code becomes unnecessary long and tedious. For such high performance cases, just use CLI approach and embed your array inserts just within your code and do mix and match of the coding. This way, you can write high performance programs.
Download Array Insert
Download Compile Commands
Column-wise Array Insert CLI/Embedded SQL Program
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlcli1.h>
#include <sql.h>
#include <sqlenv.h>
#define MAX_UID_LENGTH 18
#define MAX_PWD_LENGTH 30
#define ARRAYSIZE 10
#define ITERCOUNT 10000
#define COMMITCOUNT 100
int arraysize = ARRAYSIZE, itercount = ITERCOUNT;
int commitcount = COMMITCOUNT;
EXEC SQL INCLUDE SQLCA;
int serverTime(SQLHANDLE hdbc, char *sTime);
int HandleInfoPrint(SQLSMALLINT, SQLHANDLE, SQLRETURN, int, char *);
/*
$ db2 "create table fedsch.arraytab (c1 int not null,
c2 varchar(100) not null, c3 int,
c4 int not null generated always as identity)"
$ db2 "alter table fedsch.arraytab primary key(c4)"
*/
int rc = 0;
/* macro for environment handle checking */
#define ENV_HANDLE_CHECK(henv, cliRC) \
if (cliRC != SQL_SUCCESS) \
{ \
rc = HandleInfoPrint(SQL_HANDLE_ENV, henv, \
cliRC, __LINE__, __FILE__); \
if (rc != 0) return rc; \
}
/* macro for connection handle checking */
#define DBC_HANDLE_CHECK(hdbc, cliRC) \
if (cliRC != SQL_SUCCESS) \
{ \
rc = HandleInfoPrint(SQL_HANDLE_DBC, hdbc, \
cliRC, __LINE__, __FILE__); \
if (rc != 0) return rc; \
}
/* macro for statement handle checking */
#define STMT_HANDLE_CHECK(hstmt, hdbc, cliRC) \
if (cliRC != SQL_SUCCESS) \
{ \
rc = HandleInfoPrint(SQL_HANDLE_STMT, hstmt, \
cliRC, __LINE__, __FILE__); \
if (rc == 2) StmtResourcesFree(hstmt); \
if (rc != 0) TransRollback(hdbc); \
if (rc != 0) return rc; \
}
/* free statement handles and print unexpected occurrences */
/* this function is used in STMT_HANDLE_CHECK */
int StmtResourcesFree(SQLHANDLE hstmt)
{
SQLRETURN cliRC = SQL_SUCCESS;
int rc = 0;
/* free the statement handle */
cliRC = SQLFreeStmt(hstmt, SQL_UNBIND);
rc = HandleInfoPrint(SQL_HANDLE_STMT, hstmt, cliRC, __LINE__, __FILE__);
if (rc != 0)
{
return 1;
}
/* free the statement handle */
cliRC = SQLFreeStmt(hstmt, SQL_RESET_PARAMS);
rc = HandleInfoPrint(SQL_HANDLE_STMT, hstmt, cliRC, __LINE__, __FILE__);
if (rc != 0)
{
return 1;
}
/* free the statement handle */
cliRC = SQLFreeStmt(hstmt, SQL_CLOSE);
rc = HandleInfoPrint(SQL_HANDLE_STMT, hstmt, cliRC, __LINE__, __FILE__);
if (rc != 0)
{
return 1;
}
return 0;
} /* StmtResourcesFree */
/* rollback transactions on a single connection */
/* this function is used in HANDLE_CHECK */
void TransRollback(SQLHANDLE hdbc)
{
SQLRETURN cliRC = SQL_SUCCESS;
int rc = 0;
printf("\n Rolling back the transaction...\n");
/* end transactions on the connection */
cliRC = SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_ROLLBACK);
rc = HandleInfoPrint(SQL_HANDLE_DBC, hdbc, cliRC, __LINE__, __FILE__);
if (rc == 0)
{
printf(" The transaction rolled back.\n");
}
} /* TransRollback */
void HandleLocationPrint(SQLRETURN cliRC, int line, char *file)
{
printf(" cliRC = %d\n", cliRC);
printf(" line = %d\n", line);
printf(" file = %s\n", file);
} /* HandleLocationPrint */
void HandleDiagnosticsPrint(SQLSMALLINT htype, /* handle type identifier */
SQLHANDLE hndl /* handle */ )
{
SQLCHAR message[SQL_MAX_MESSAGE_LENGTH + 1];
SQLCHAR sqlstate[SQL_SQLSTATE_SIZE + 1];
SQLINTEGER sqlcode;
SQLSMALLINT length, i;
i = 1;
/* get multiple field settings of diagnostic record */
while (SQLGetDiagRec(htype,
hndl,
i,
sqlstate,
&sqlcode,
message,
SQL_MAX_MESSAGE_LENGTH + 1,
&length) == SQL_SUCCESS)
{
printf("\n SQLSTATE = %s\n", sqlstate);
printf(" Native Error Code = %d\n", sqlcode);
printf("%s\n", message);
i++;
}
printf("-------------------------\n");
} /* HandleDiagnosticsPrint */
/* outputs to screen unexpected occurrences with CLI functions */
int HandleInfoPrint(SQLSMALLINT htype, /* handle type identifier */
SQLHANDLE hndl, /* handle used by the CLI function */
SQLRETURN cliRC, /* return code of the CLI function */
int line,
char *file)
{
int rc = 0;
switch (cliRC)
{
case SQL_SUCCESS:
rc = 0;
break;
case SQL_INVALID_HANDLE:
printf("\n-CLI INVALID HANDLE-----\n");
HandleLocationPrint(cliRC, line, file);
rc = 1;
break;
case SQL_ERROR:
printf("\n--CLI ERROR--------------\n");
HandleLocationPrint(cliRC, line, file);
HandleDiagnosticsPrint(htype, hndl);
rc = 2;
break;
case SQL_SUCCESS_WITH_INFO:
rc = 0;
break;
case SQL_STILL_EXECUTING:
rc = 0;
break;
case SQL_NEED_DATA:
rc = 0;
break;
case SQL_NO_DATA_FOUND:
rc = 0;
break;
default:
printf("\n--default----------------\n");
HandleLocationPrint(cliRC, line, file);
rc = 3;
break;
}
return rc;
} /* HandleInfoPrint */
int CLIAppInit(char dbAlias[],
char user[],
char pswd[],
SQLHANDLE *pHenv,
SQLHANDLE *pHdbc,
SQLPOINTER autocommitValue)
{
SQLRETURN cliRC = SQL_SUCCESS;
int rc = 0;
/* allocate an environment handle */
cliRC = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, pHenv);
if (cliRC != SQL_SUCCESS)
{
printf("\n--ERROR while allocating the environment handle.\n");
printf(" cliRC = %d\n", cliRC);
printf(" line = %d\n", __LINE__);
printf(" file = %s\n", __FILE__);
return 1;
}
/* set attribute to enable application to run as ODBC 3.0 application */
cliRC = SQLSetEnvAttr(*pHenv,
SQL_ATTR_ODBC_VERSION,
(void *)SQL_OV_ODBC3,
0);
ENV_HANDLE_CHECK(*pHenv, cliRC);
/* allocate a database connection handle */
cliRC = SQLAllocHandle(SQL_HANDLE_DBC, *pHenv, pHdbc);
ENV_HANDLE_CHECK(*pHenv, cliRC);
/* set AUTOCOMMIT off or on */
cliRC = SQLSetConnectAttr(*pHdbc,
SQL_ATTR_AUTOCOMMIT,
autocommitValue,
SQL_NTS);
DBC_HANDLE_CHECK(*pHdbc, cliRC);
printf("\n Connecting to %s...", dbAlias);
/* connect to the database */
cliRC = SQLConnect(*pHdbc,
(SQLCHAR *)dbAlias,
SQL_NTS,
(SQLCHAR *)user,
SQL_NTS,
(SQLCHAR *)pswd,
SQL_NTS);
DBC_HANDLE_CHECK(*pHdbc, cliRC);
printf(" Connected to %s.\n", dbAlias);
return 0;
} /* CLIAppInit */
/* terminate a CLI application by:
o disconnecting from the database
o freeing the connection handle
o freeing the environment handle */
int CLIAppTerm(SQLHANDLE * pHenv, SQLHANDLE * pHdbc,
char dbAlias[])
{
SQLRETURN cliRC = SQL_SUCCESS;
int rc = 0;
printf("\n Disconnecting from %s...", dbAlias);
/* disconnect from the database */
cliRC = SQLDisconnect(*pHdbc);
DBC_HANDLE_CHECK(*pHdbc, cliRC);
printf(" Disconnected\n");
/* free connection handle */
cliRC = SQLFreeHandle(SQL_HANDLE_DBC, *pHdbc);
DBC_HANDLE_CHECK(*pHdbc, cliRC);
/* free environment handle */
cliRC = SQLFreeHandle(SQL_HANDLE_ENV, *pHenv);
ENV_HANDLE_CHECK(*pHenv, cliRC);
return 0;
} /* CLIAppTerm */
int main(int argc, char *argv[])
{
int rc = 0;
SQLHANDLE henv; /* environment handle */
SQLHANDLE hdbc; /* connection handle */
char dbAlias[SQL_MAX_DSN_LENGTH + 1];
char user[MAX_UID_LENGTH + 1];
char pswd[MAX_PWD_LENGTH + 1];
char sTime[27];
if (argc == 1)
{
arraysize = ARRAYSIZE;
itercount = ITERCOUNT;
commitcount = COMMITCOUNT;
} else if (argc == 4)
{
arraysize = atoi(argv[1]);
itercount = atoi(argv[2]);
commitcount = atoi(argv[3]);
} else
{
printf("Usage %s arraysize itercount, commitcount\n", argv[0]);
exit(-1);
}
strcpy(dbAlias, "SAMPLE");
strcpy(user,"");
strcpy(pswd,"");
/* Init CLI */
rc = CLIAppInit(dbAlias,
user,
pswd,
&henv,
&hdbc,
(SQLPOINTER)SQL_AUTOCOMMIT_OFF);
if (rc != 0)
{
return rc;
}
serverTime(hdbc, sTime);
printf ("Begin Server time %s\n", sTime);
rc = arrayInsert(henv, hdbc);
serverTime(hdbc, sTime);
printf ("End Server time %s\n", sTime);
/* End CLI */
rc = CLIAppTerm(&henv, &hdbc, dbAlias);
return rc;
} /* main */
int arrayInsert(SQLHANDLE henv, SQLHANDLE hdbc)
{
int i, j;
SQLRETURN cliRC = SQL_SUCCESS;
SQLHANDLE hstmt; /* Statement handle */
SQLCHAR stmt[2000000];
SQLLEN c1[arraysize];
SQLCHAR c2[arraysize][101];
SQLLEN c3[arraysize];
strcpy(stmt, "insert into fedsch.arraytab (c1,c2,c3) values (?,?,?)");
/* set AUTOCOMMIT OFF */
cliRC = SQLSetConnectAttr(hdbc,
SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)SQL_AUTOCOMMIT_OFF,
SQL_NTS);
DBC_HANDLE_CHECK(hdbc, cliRC);
/* allocate the handle for statement */
cliRC = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
DBC_HANDLE_CHECK(hdbc, cliRC);
/* Prepare the statement */
cliRC = SQLPrepare( hstmt, stmt, SQL_NTS ) ;
STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);
/* Set Arraysize for buffered insert */
cliRC = SQLSetStmtAttr( hstmt,
SQL_ATTR_PARAMSET_SIZE,
(SQLPOINTER) arraysize,
0
) ;
STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);
for (i = 0; i < itercount; ++i)
{
for (j = 0; j < arraysize; ++j)
{
c1[j] = i * arraysize + j + 1000;
sprintf(c2[j], "ARRAY %d %d", i, j);
c3[j] = i * arraysize + j;
}
cliRC = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG,
SQL_INTEGER, 0, 0, c1, 0, NULL);
STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);
cliRC = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_VARCHAR, 100, 0, c2, 101, NULL);
STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);
cliRC = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_SLONG,
SQL_INTEGER, 0, 0, c3, 0, NULL);
STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);
cliRC = SQLExecute( hstmt ) ;
STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);
if (i > 0 && i % commitcount == 0)
{
/* Commit transactions */
cliRC = SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT);
DBC_HANDLE_CHECK(hdbc, cliRC);
}
}
/* Commit transactions */
cliRC = SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT);
DBC_HANDLE_CHECK(hdbc, cliRC);
/* free the statement handle */
cliRC = SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);
return 0;
}
/* return Server Time using embedded SQL */
int serverTime(SQLHANDLE hdbc, char *sTime)
{
SQLRETURN cliRC = SQL_SUCCESS;
/* set AUTOCOMMIT OFF */
cliRC = SQLSetConnectAttr(hdbc,
SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)SQL_AUTOCOMMIT_OFF,
SQL_NTS);
DBC_HANDLE_CHECK(hdbc, cliRC);
EXEC SQL BEGIN DECLARE SECTION;
char timestamp[27];
EXEC SQL END DECLARE SECTION;
EXEC SQL VALUES CURRENT TIMESTAMP INTO :timestamp;
if (SQLCODE != 0)
{
strcpy(sTime, "-1");
return -1;
}
/* end transactions on a connection */
cliRC = SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT);
DBC_HANDLE_CHECK(hdbc, cliRC);
strcpy(sTime, timestamp);
return 0;
}
Prerequisite for program
$ db2 connect to sample
$ db2 -tf ~/sqllib/misc/EXPLAIN.DDL
$ db2 "create table fedsch.arraytab (c1 int not null,
c2 varchar(100) not null, c3 int,
c4 int not null generated always as identity)"
$ db2 "alter table fedsch.arraytab primary key(c4)"
$ db2 connect reset
Compile the program
$ cat prepcmd
PACKAGETOKEN=$(date +"%j%M%S")
db2 -v connect to $1
db2 -v "prep $2.sqc bindfile explain yes explsnap yes package using P$PACKAGETOKEN preprocessor 'gcc -I$HOME/sqllib/include -E -o $2.i'"
gcc $2.c -L$HOME/sqllib/lib -ldb2 -o $2prg
db2 -v connect reset
$ ./prepcmd sample climerge
connect to sample
Database Connection Information
Database server = DB2/LINUXX8664 9.1.2
SQL authorization ID = VIKRAM
Local database alias = SAMPLE
prep climerge.sqc bindfile explain yes explsnap yes package using P1374235 preprocessor 'gcc -I/home/vikram/sqllib/include -E -o climerge.i'
LINE MESSAGES FOR climerge.sqc
------ --------------------------------------------------------------------
SQL0060W The "C" precompiler is in progress.
SQL4017W Preprocessing has completed successfully.
SQL4018W Starting to process the preprocessed file
"/home/vikram/test/climerge.i".
SQL4019W Completed processing the preprocessed file
"climerge.i".
SQL0091W Precompilation or binding was ended with "0"
errors and "0" warnings.
connect reset
DB20000I The SQL command completed successfully.
Run the program
$ ./climergeprg Connecting to SAMPLE... Connected to SAMPLE. Begin Server time 2007-05-17-22.43.45.494930 End Server time 2007-05-17-22.43.49.686326 Disconnecting from SAMPLE... Disconnected
In above program, we capture the server time before and after the test using embedded SQL statements whereas the column wise array insert is performed using array insert capability of DB2 CLI.