KR-163: -818 and 81Q!

 

Sometimes while running a COBOL-DB2 code one might get a -818 error on the first SQL statement that is executed in the code. 

Idea for this KR triggered by Vignesh and Aalok.

SQLCODE = -818, ERROR: The precompiler-generated Timestamp xxxxx in the load module is different from the the BIND TIMESTAMP yyyyy from the DBRM...
Or in case you don't have the DB2 error handling routine (DSNTIAR is what displays the error messages in the spool), if you give a display of the SQLCODE you'll get a 
81Q in the spool.

To know what is the actual value for the display "81Q" we'll need to convert this (converting to hex will reveal the actual value). We can open any flat file in edit mode, type 81Q and then give the command HEX ON. The equivalent hex values would appear below 81Q and it will be F8F1D8 which is equal to -818 (D denotes a negative sign).

-818 error signifies that the timestamp is different in the load module and the DBRM. Ideally both should be same; 
First question: From where does this timestamp come in the picture?

• When we precompile a COBOL-DB2 code, the precompiler creates 2 outputs: the application program and the DBRM. The precompiler places a timestamp in both these outputs. 
• The application program: In this the SQL statements will have been commented and some CALL statements will have been inserted by the precompiler (the CALL statements will call some DB2 modules). 
• DBRM: Database Request Module contains the SQLs. 
• The application program is now just like a simple cobol program which is passed through the COBOL compiler and then the linker to create the load module. This load module contains the executable version of the COBOL statements. 
• But if we attempt to run this module how will the SQLs get executed? We need something else along with this load module because the SQLs are contained elsewhere; they weren't in the application program. 
• So we need a couple of things to execute the program; one is the load module and the other will be the "plan" (which comes into picture in the process of binding). 
• When we bind a DBRM to a plan (also called execution plan), the timestamp of the DBRM is noted within the plan as well. 
• During binding a lot of specific DB2 issues are checked (like column names used, syntax of the SQLs etc.) and the way in which the data should be retrieved is determined. 
• Thus to execute a COBOL-DB2 program we mention the load module and the execution plan. And when the code encounters the first SQL a check is performed to check if the load module timestamp is the same as the DBRM timestamp (if it isn't it means that the load module and DBRM were not created at the same time).

That's some of the theory on precompiling and binding; so why won't timestamps match? And how / where can we see them?
We're running out of space in this edition; will be continued in the next...

 

---

KR-164: -818 part II

 

Continuing with -818 sqlcode;
We left off yesterday with the question, "Why won't the two timestamps match?" (i..e the DBRM and the load module's timestamps). Following are some possible reasons:

1.) We ran through the sequence of precompile, compile, link and bind once. Then again we ran only the precompile step. Here the DBRM will have a new timestamp while the load module will be old.
2.) The precompiler produces a DBRM and application program. We might have referred to a different PDS while compiling.
3.) Precompile, compile, link done but bind not done. Here the load module will have the latest timestamp while the plan is an old one (because binding wasn't done).
4.) We did everything but the precompiler didn't replace the existing DBRM. So the DBRM will have a old timestamp while the load module will have the new one.

Solutions?

• Point 3 is a common cause of -818 and binding the plan will solve the problem. 
• For points 1 and 2 we just need to ensure that all steps have run properly (return code <= 4 for precompile, compile, link and bind) and that each step refers to the correct PDSes (for ex.: the output of the precompiler should be used as input to the compiler etc.). 
• But sometimes we would have done everything correctly and yet we get a -818 on running the code. Point number 4 is something we tend to face once in while. 
• It just sometimes happens that the DBRM might not get replaced in the DBRM pds (even though no error pops up during precompile stage).

What to do if we face problem number 4?

• Delete the load module and DBRM from the respective PDSes for the code causing the problem. 
• The DBRM and load get created during the generation process, so no problems arise by deleting them (as long as you delete them from the right place; if you don't feel comfortable with deleting, then take a backup of them or rename them). 
• Now run through the entire process again (i.e. precompile, compile, link and bind).

Well, next question is "that's fine but where can we see what this timestamp is"? Can we try to match the timestamps?"

More on that in the next edition;

---

KR-165: -818 part III

 

We'll dig further into the DBRM and load module timestamps and internals of how all this works.

• DB2 maintains a lot of useful info in its catalogs - regarding indexes, tables, plans, DBRMs etc. 
• And most of the catalog info is available in DB2 tables themselves. So why not take a look into these tables and learn more on timestamps.

Some things we need to know:

• The DBRM details are stored in the DB2 table: SYSIBM.SYSDBRM 
• The plan details are stored in the DB2 table: SYSIBM.SYSPLAN 
• Find out the PDS containing the load module of the code you are investigating (this can be found easily from the execution/compilation JCL for that code).

Note: The timestamp is actually not in proper readable format (in the sense that we can't directly interpret the date/time from it). They are internal DB2 timestamps.

• Let's take an example of a code say EMPUPDT which uses a plan called EMPPLAN. We perform the various steps: precompile, compile, link and bind on this code. 
• The DBRM produced is also called EMPUPDT. 
• In the SYSDBRM table we can query using the DBRM name: EMPUPDT. 
• This will give us the plan used for binding this DBRM along with the precompile date/time and also the internal timestamp (which can be read in HEX format).
  SELECT NAME,HEX(TIMESTAMP) 
  FROM SYSIBM.SYSDBRM 
  WHERE NAME='EMPUPDT' 

The timestamp will appear in 16 characters - this is the internal DB2 timestamp which we need. It might appear as:
TIMESTAMP (from SYSDBRM): 17C3C25806552EE8

Note: The timestamp we get here has 16 hex characters. If in our query we didn't use the HEX function then we'll get eight characters on the screen.
To be continued...(and do try out the query on your system);