KR-166: -818 part IV

 

In the first edition of 2006 we continue with our exploration of -818 SQLCODE:

SELECT NAME,HEX(TIMESTAMP) 
FROM SYSIBM.SYSDBRM 
WHERE NAME='EMPUPDT' 

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

Also there are columns which provide the precompile time and date etc. For example:
PRECOMPTS: 2005-12-26-05.32.47.566011 (gives the precompile time).

Next question is where is this physically stored?
From the precompile JCL you can find out the PDS where the DBRM was created. Or even in the SYSDBRM table we have a column called PDSNAME which will contain the name of the PDS where the DBRM exists. 

Go to this PDS and open the member EMPUPDT in view mode. 
Now try to find the timestamp here. Command for finding hex characters is:
F x'17C3C25806552EE8'

and voila! It'll appear (it'll appear as junk characters so type HEX ON to see the complete hex value).

So now we know where in the DBRM the timestamp is stored and how to check it.

Similarly in the table SYSIBM.SYSPLAN if we query for:
NAME='EMPPLAN'
we'll get details on when the plan was bound. Something like:
BOUNDTS: 2005-12-26-05.32.50.735223

Next we need to locate the timestamp in the load module. That we shall see in the forthcoming editions...

---

KR-167: -818 part V

 

Continuing with the -818 story:
Next we need to look into the load module. 

• The load module PDS can be found from the JCL used for linking the code (the DD name would be SYSLMOD). 
• Go to this PDS and open the EMPUPDT member. This will by default open in browse mode. 
• Here search for 2005 and you'll find the date hidden somewhere. Is this a timestamp? 
• This is a timestamp placed during the time of linking (when the load module is created) but this isn't the DB2 internal timestamp. 
• It's quite obvious because the value we see here will be something like:
  EMPUPDT20051226054355030200
  This is pretty much readable (year/month/date/time format).

A quick way of figuring out the problem when -818 is encountered:

• When we get a -818 it means that the load and dbrm were created at different times. 
• From the load module we get the date and time as to when the load was created and from the SYSDBRM we'll get the time when the code was precompiled and bound (we're not referring to the internal timestamp; we're referring to the directly readable time fields). 
• If we had run the precompile, compile, link, bind steps in continuous sequence then these two values should be approximately close (i.e. the date should be the same and time would be fairly close as well). 
• If it isn't (for instance if the DBRM weren't created), then the two will differ significantly.

Now you might ask, "that's good but where's this internal timestamp stored in the load module"?
To be continued...

 

---

KR 168: -818 part VI 

 

After a brief hiatus of 3 weeks KRs are back.

 

We continue with the -818 error...

• Rewinding back you will remember that all SQL statements are commented out by the precompiler in the application program and some CALL statements are added (to call some DB2 modules) in place of them.
• If we take a closer look at the output of the precompiler (or the input to the COBOL compiler) we'll find some interesting things done by the precompiler.
• We'll find that the precompiler calls a DB2 module by passing some structure to the DB2 module (and these structures will differ for each SQL statement).
• A couple of fields in these structures are:
  SQL-TIMESTAMP-1 PIC S9(9) COMP-4 VALUE +398705240.
  SQL-TIMESTAMP-2 PIC S9(9) COMP-4 VALUE +106245864.
• What's a COMP-4? (we've seen this in an earlier KR also; it's the same as COMP or binary format).
• Thus every SQL statement in the code will have a corresponding unique structure (this structure also contains the program name and other info - like what sql statement it is etc.). This timestamp is the internal timestamp.
• These 2 values can be found in the load module. How?
• Before we start searching for them in the load module we need to convert them from decimal to hex format; we could do it manually or use some help from Windows.

We'll conclude this in the next edition; for now do take a look into the structure that is created for each SQL statement by the precompiler; you'll find a lot of interesting things there.