DB2 provides a tool db2dclgn that you can use effectively as an aid in your C/C++ or Java coding efforts. The example shown here is for embedded SQL in C.
Create a table for demo
CREATE TABLE "EXE"."CUTION" ( "UT_UD" BIGINT NOT NULL , "UT_ALLOC_ACCT_UD" BIGINT , "UT_APP_ID" VARCHAR(32) , "UT_BRKR_PARTY_UD" BIGINT , "UT_CAP_UD" BIGINT , "UT_CLEAR_PARTY_UD" BIGINT , "UT_CLT_PARTY_UD" BIGINT , "UT_CMT_TXT" VARCHAR(128) , "UT_CNTRA_BRKR_PARTY_UD" BIGINT , "UT_CNTRA_CLRFM_PARTY_UD" BIGINT , "UT_CNTRA_TRDR_ID" VARCHAR(32) , "UT_CROSS_TYP_UD" BIGINT , "UT_DAY_AVG_PX" DOUBLE , "UT_DAY_CUM_QT" DOUBLE , "UT_DESK_UD" BIGINT , "UT_EVENT_TRANS_TS" TIMESTAMP , "UT_EVENT_TYP_UD" BIGINT , "UT_EXEXGRP_UD" BIGINT , "UT_UT_ID" VARCHAR(64) , "UT_GVUP_BRKR_PARTY_UD" BIGINT , "UT_INST_UD" BIGINT , "UT_MKT_LQDTY_UD" BIGINT , "UT_PX" DOUBLE , "UT_PX_CCY_UD" BIGINT , "UT_RSPBL_TRDR_PRSN_UD" BIGINT , "UT_SETTL_CCY_UD" BIGINT , "UT_SETTL_TYP_UD" BIGINT , "UT_SHARE_QT" DOUBLE , "UT_SIDE_UD" BIGINT , "UT_ORD_AVG_PX" DOUBLE , "UT_ORD_CUM_QT" DOUBLE , "UT_TRAD_ACCT_UD" BIGINT , "UT_TRANS_STAT_UD" BIGINT , "UT_TRANS_TYP_UD" BIGINT , "UT_UPSTRM_SYS_UD" BIGINT , "UT_TRD_DATE_YMD_PART" INTEGER NOT NULL , "UT_ENTRY_MINUTE_PART" SMALLINT , "UT_MSG_JNL_UD" BIGINT , "UT_CR_PROC_ID" VARCHAR(32) NOT NULL , "UT_CR_USER_ID" VARCHAR(32) NOT NULL , "UT_CR_TS" TIMESTAMP NOT NULL , "UT_SUBSYS_UD" BIGINT , "UT_DNSTRM_SYS_UD" BIGINT , "UT_MKT_SYS_UD" BIGINT ); INSERT INTO EXE.CUTION VALUES (1234567890,987463453,'UTILITY',4353262, NULL,NULL,NULL,'How are you', NULL,353652326,'STATION',643632, 5325.436436,457437.437437,326326326,CURRENT TIMESTAMP, NULL,NULL,'DURHAM BULL',43643373, NULL,NULL,NULL,NULL, 3532525,3636363,34335,365363.4848,3636262, 363632.36363,4874743.37373,436363262,3636226, NULL,NULL,3242,23, NULL,'DB2UDE','DUKE',CURRENT TIMESTAMP, 3532652,4636326,4763763); INSERT INTO EXE.CUTION VALUES (35367890,987463453,'SOOKING',4353262, NULL,NULL,NULL,'I am good', NULL,353652326,'STATION',643632, 5325.436436,457437.437437,326326326,CURRENT TIMESTAMP, NULL,NULL,'DURHAM BULL',43643373, NULL,NULL,NULL,NULL, 3532525,3636363,4535327,365363.4848,3636262, 363632.36363,4874743.37373,436363262,3636226, NULL,NULL,3242,23, NULL,'DB2UDE','DUKE',CURRENT TIMESTAMP, 3532652,4636326,4763763);
Use DB2DCLGN Tool
After creating above table, let us use db2dclgn tool to generate a C structure that we can use in our C program.
$ db2dclgn -d sample -t exe.cution -i -l C -o exe_cution.h
What did we do above? We used db2dclgn tool to generate a C structure for table exe.cution for language C including indicator variables and output was saved in exe_cution.h file.
[vikram@gopal test]$ cat exe_cution.h
struct
{
sqlint64 ut_ud;
sqlint64 ut_alloc_acct_ud;
struct
{
short length;
char data[32];
} ut_app_id;
sqlint64 ut_brkr_party_ud;
sqlint64 ut_cap_ud;
sqlint64 ut_clear_party_ud;
sqlint64 ut_clt_party_ud;
struct
{
short length;
char data[128];
} ut_cmt_txt;
sqlint64 ut_cntra_brkr_party_ud;
sqlint64 ut_cntra_clrfm_party_ud;
struct
{
short length;
char data[32];
} ut_cntra_trdr_id;
sqlint64 ut_cross_typ_ud;
double ut_day_avg_px;
double ut_day_cum_qt;
sqlint64 ut_desk_ud;
char ut_event_trans_ts[27];
sqlint64 ut_event_typ_ud;
sqlint64 ut_exexgrp_ud;
struct
{
short length;
char data[64];
} ut_ut_id;
sqlint64 ut_gvup_brkr_party_ud;
sqlint64 ut_inst_ud;
sqlint64 ut_mkt_lqdty_ud;
double ut_px;
sqlint64 ut_px_ccy_ud;
sqlint64 ut_rspbl_trdr_prsn_ud;
sqlint64 ut_settl_ccy_ud;
sqlint64 ut_settl_typ_ud;
double ut_share_qt;
sqlint64 ut_side_ud;
double ut_ord_avg_px;
double ut_ord_cum_qt;
sqlint64 ut_trad_acct_ud;
sqlint64 ut_trans_stat_ud;
sqlint64 ut_trans_typ_ud;
sqlint64 ut_upstrm_sys_ud;
sqlint32 ut_trd_date_ymd_part;
short ut_entry_minute_part;
sqlint64 ut_msg_jnl_ud;
struct
{
short length;
char data[32];
} ut_cr_proc_id;
struct
{
short length;
char data[32];
} ut_cr_user_id;
char ut_cr_ts[27];
sqlint64 ut_subsys_ud;
sqlint64 ut_dnstrm_sys_ud;
sqlint64 ut_mkt_sys_ud;
} cution;
short ind_cution[44];
If you look at above code, it is a long C structure that corresponds to EXE.CUTION table. Imagine – if you have to hand code this structure, it might be error prone and long hand coding.
How to use DB2DCLGN structure in your program
Let us show how can you use above structure in your program?
$ cat cution.sqc
#include <stdio.h>
#include <string.h>
#include <sqlca.h>
int main()
{
int i, j;
EXEC SQL INCLUDE SQLCA;
EXEC SQL BEGIN DECLARE SECTION;
sqlint64 ut_ud;
char sqlBuffer[1000];
#include "exe_cution.h"
EXEC SQL END DECLARE SECTION;
strcpy(sqlBuffer, "SELECT * FROM EXE.CUTION WHERE UT_UD = ?");
ut_ud = 1234567890;
EXEC SQL WHENEVER SQLERROR GOTO error_section;
EXEC SQL CONNECT TO sample;
EXEC SQL PREPARE dynquery FROM :sqlBuffer;
EXEC SQL DECLARE cur_1 CURSOR FOR dynquery;
EXEC SQL OPEN cur_1 USING :ut_ud;
EXEC SQL FETCH cur_1 INTO :cution :ind_cution;
printf("UT_CR_TS = %s UT_UD = %ld\n", cution.ut_cr_ts, cution.ut_ud);
EXEC SQL CLOSE cur_1;
EXEC SQL CONNECT RESET;
return 0;
error_section:
{
printf ("Error occured SQLCODE = %d\n", sqlca.sqlcode);
return -1;
}
}
If you look above program, we have included the table structure for EXE.CUTION in HOST DECLARE section using include directive.
EXEC SQL BEGIN DECLARE SECTION;
sqlint64 ut_ud;
char sqlBuffer[1000];
#include "exe_cution.h"
EXEC SQL END DECLARE SECTION;
How to compile?
But if you try to pre-compile above program in a regular way using DB2 PREP command as shown below, you will get this error.
$ db2 connect to sample
$ db2 prep cution.sqc bindfile
LINE MESSAGES FOR cution.sqc
------ --------------------------------------------------------------------
SQL0060W The "C" precompiler is in progress.
26 SQL0324N The "host" variable ":ind_cution" is the wrong
type.
SQL0095N No bind file was created because of previous
errors.
SQL0091W Precompilation or binding was ended with "2"
errors and "0" warnings.
The DB2 PREP command is complaining that it is not able to understand ind_cution host variable. It is because, since DB2 PREP command did not include exe_cution.h file before processing it. You have to invoke C preprocessor before PREP so that it understands about the C structure that we are including. Let us try it differently.
$ db2 "prep cution.sqc preprocessor 'gcc -I$HOME/sqllib/include -E -o cution.i'"
LINE MESSAGES FOR cution.sqc
------ --------------------------------------------------------------------
SQL0060W The "C" precompiler is in progress.
SQL4017W Preprocessing has completed successfully.
SQL4018W Starting to process the preprocessed file
"/home/vikram/test/cution.i".
SQL4019W Completed processing the preprocessed file
"cution.i".
SQL0091W Precompilation or binding was ended with "0"
errors and "0" warnings.
This time, the PREP did its job correctly since we invoked C preprocessor using preprocessor switch in the PREP command and invoked gcc with -E switch and sent the output to cution.i file so that it can be used for later compilation.
You can use following script to compile the program. Please refer to this article for details on using some tricks in successfully using PREP command when KEEPFENCED is set to YES.
$ 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
Compile and run the program
$ db2 connect to sample $ db2 -tf ~/sqllib/misc/EXPLAIN.DDL
$ ./prepcmd sample cution
connect to sample
Database Connection Information
Database server = DB2/LINUXX8664 9.1.2
SQL authorization ID = VIKRAM
Local database alias = SAMPLE
prep cution.sqc bindfile explain yes explsnap yes package using P1404303 preprocessor 'gcc -I/home/vikram/sqllib/include -E -o cution.i'
LINE MESSAGES FOR cution.sqc
------ --------------------------------------------------------------------
SQL0060W The "C" precompiler is in progress.
SQL4017W Preprocessing has completed successfully.
SQL4018W Starting to process the preprocessed file
"/home/vikram/test/cution.i".
SQL4019W Completed processing the preprocessed file
"cution.i".
SQL0091W Precompilation or binding was ended with "0"
errors and "0" warnings.
connect reset
DB20000I The SQL command completed successfully.
$ ./cutionprg UT_CR_TS = 2007-05-20-08.03.07.401673 UT_UD = 1234567890
If you notice that our program is simple and short. We dumped whole row using a single FETCH statement in a C structure that we had created using DB2DCLGN tool. You can use same tool for generating Java code if you are using SQLJ (embedded SQL in Java).
EXEC SQL FETCH cur_1 INTO :cution :ind_cution;
Did you get db2ude?