Declaring host variable (The One without DCLGEN.)

DCLGEN (Declaration generator) is the utility/tool provided by IBM to create the Host Language (Cobol) compatible variable for columns in DB2 table.

It's simple ,you just provide the table name you are using in your program,DCLGEN will create the equivalent Host variable for you.

Can you, without the help of DCLGEN,create similar structure of host variable manually ??

Yes, off-course you can, Provided you are able to convert the data types and length from db2 column to that of cobol Program. For Ex. CHAR(10) to PIC X(10),SMALLINT to PIC S9(4) COMP etc.

Having said that, now consider the below scenario

I have created the host variable manually and used it in query which is embedded in my application Program.

Here it goes..

Declaration of Host variable:

     05 WS-STAT-C  PIC X(1)

Usage in Query : (ACC_STAT_C is defined as CHAR(1) NOT NULL in RCAV0300)

WHERE ACC_N ='1234'

Now when i execute the Program (Containing above query),i am getting the below error.

"Undefined or Unusable Host Variable WS-STAT-C".

Can anyone one tell me what is the problem here?? Why am i getting this error??

Well i have spelled, defined and used the variable WS-STAT-C correctly in my Program still i am getting the above mentioned error.
Lesson Learnt :

Whenever you define the host variable in your program(the one without DCLGEN) ,define it at 01 Level.If you define it at any sub-level (05,10 etc), you will get the error at run time as "Undefined or Unusable Host Variable",  even though you have spelled,defined and used the host variable correctly in your program.

Needless to say now ,above  problem can be resolved by declaring your variable in working storage section as 

01 WS-STAT-C  PIC X(1).

Where and how to check for Consistency token

As a DB2 programmer you know what consistency token is and why DB2 uses it . If not, those famous SQLCODE -805 OR -818 error will make you know what it is. So let's not get into that.

The real question is where and how I can view this unique token generated by DB2 so that I can verify that it is in sync or if not in sync then who is at the fault and how to rectify it.

So After DB2 precompiler generates this unique consistency token, it will placed and available in OBJLIB,DBRMLIB and LOAD Module library and get matched against IBM DB2 catalog table SYSIBM.SYSPACKAGE at run time.

Checking Consistency token in DBRMLIB

Open DBRMLIB library, Put command "HEX ON "  on command line and look at the position 25 to 32 at the very first record.That's your Consistency token ,

So here 19C173C91588C612 is the hex value of your contoken.

Checking Consistency token in LOADMODULE

So this token we found in DBRM lib will be present in Load module. Well , how to find it?

Unlike ,DBRMLIB there is no specific location in Load library where this token can be found.

So get the token we found in DBRMLIB ,19C173C91588C612 

Now divide into half and reverse it  that is 1588C61219C173C9.

Now Open Load library and put command "F X'1588C61219C173C9'". That's how you check it here.

Checking Consistency token in SYSIBM.SYSPACKAGE.

This is very straight forward simply run the below query.

                 SUBSTR(HEX(A.CONTOKEN),9,8) || SUBSTR(HEX(A.CONTOKEN),1,8 )
               ) AS CONTOKEN  

**ABCD1234 is your program name.

Hope you got something out of this post.