Posted: Tue Apr 24, 2018 9:33 am Post subject: Why DB2 checks consistency token for every SQL execution
Hello,
I wrote below code for testing purpose.
I received -805 during the 2nd execution of the query,
Does db2 know that I have changed the consistency token in program and hence checks for it during 2nd execution of the query?
Does it checks for the token in every execution? If yes then why?
i.e if a query is in a loop then db2 will check the token in every occurrence even though the query is same?
Code:
PROCEDURE DIVISION.
-----------------------------------
0000-MAINLINE.
PERFORM 1000-TEST
THRU 1000-EXIT
ADD +1 TO SQL-TIMESTAMP-1
PERFORM 1000-TEST
THRU 1000-EXIT
GOBACK
.
0000-EXIT.
EXIT.
1000-TEST.
EXEC SQL
SELECT CURRENT_TIMESTAMP
INTO :WS-DB2-TIMESTAMP
FROM SYSIBM.SYSDUMMY1
END-EXEC
MOVE SQLCODE TO WS-SQLCODE
EVALUATE WS-SQLCODE
WHEN 0
DISPLAY 'WS-SQLCODE :' WS-SQLCODE
WHEN +100
DISPLAY 'ROW NOT FOUND'
WHEN OTHER
DISPLAY 'WS-SQLCODE : ' WS-SQLCODE
END-EVALUATE
.
1000-EXIT.
EXIT.
Joined: 26 Nov 2002 Posts: 12388 Topics: 75 Location: San Jose
Posted: Tue Apr 24, 2018 2:42 pm Post subject:
rsantosh,
Are you compiling the program every time you run? You also need to bind the PLAN every time you compile. what are your BIND parameters? _________________ Kolusu
www.linkedin.com/in/kolusu
No. I compiled just once and bound the package once.
For first call of 1000- it gives SQLCODE 0, for the 2nd call of 1000- before which I put ADD statement to change con token it gave -805.
I am calling 1000- twice in the 0000- para.
Will provide BIND. Parameter later in the day.
Santosh _________________ Regards, Santosh
Yes, so that means package wouldnt have gotten created had there been any error during bind. I have bind access. When first time I executed the program I received -805 at the first call of 1000- para itself because I forgot to bind the package. Then i did the bind and received 0 at first call and -805 at 2nd call.
I will put the display and share the sysout with you.
rsantosh,
Also can you add DISPLAY statements before invoking the paragraphs?
Code:
0000-MAINLINE.
DISPLAY 'BEFORE 1ST CALL TO 1000- PARA'
PERFORM 1000-TEST
THRU 1000-EXIT
DISPLAY 'AFTER 1ST CALL TO 1000- PARA'
ADD +1 TO SQL-TIMESTAMP-1
DISPLAY 'BEFORE 2ND CALL TO 1000- PARA'
PERFORM 1000-TEST
THRU 1000-EXIT
DISPLAY 'AFTER 2ND CALL TO 1000- PARA'
GOBACK
.
0000-EXIT.
EXIT.
1000-TEST.
DISPLAY 'INSIDE 1000- PARA'
EXEC SQL
SELECT CURRENT_TIMESTAMP
INTO :WS-DB2-TIMESTAMP
FROM SYSIBM.SYSDUMMY1
END-EXEC
MOVE SQLCODE TO WS-SQLCODE
EVALUATE WS-SQLCODE
WHEN 0
DISPLAY 'WS-SQLCODE :' WS-SQLCODE
WHEN +100
DISPLAY 'ROW NOT FOUND'
WHEN OTHER
DISPLAY 'WS-SQLCODE : ' WS-SQLCODE
END-EVALUATE
.
1000-EXIT.
EXIT.
,XXXX,OUTPUT DISPLAY XXXXXXXX JOB14008 101 LINE 0 COLUMNS 02- 81
,COMMAND INPUT ===>, ,SCROLL ===>,CSR ,
********************************* TOP OF DATA **********************************
BEFORE 1ST CALL TO 1000- PARA
INSIDE 1000- PARA
WS-SQLCODE :0000
AFTER 1ST CALL TO 1000- PARA
BEFORE 2ND CALL TO 1000- PARA
INSIDE 1000- PARA
WS-SQLCODE : 080N
AFTER 2ND CALL TO 1000- PARA
******************************** BOTTOM OF DATA ********************************
Joined: 26 Nov 2002 Posts: 12388 Topics: 75 Location: San Jose
Posted: Thu Apr 26, 2018 10:54 am Post subject:
rsantosh,
Just before the EXEC SQL statement add the following statement
Code:
INITIALIZE WS-DB2-TIMESTAMP
What exactly are you trying to do adding +1 to SQL-TIMESTAMP-1 ? Is SQL-TIMESTAMP-1 a redefined version of WS-DB2-TIMESTAMP? If your intention is add 1 sec then you can do it directly like this
Code:
SELECT CURRENT_TIMESTAMP + 1 SECOND
INTO :WS-DB2-TIMESTAMP
FROM SYSIBM.SYSDUMMY1
or adding microsecond.
Code:
SELECT CURRENT_TIMESTAMP + 1 MICROSECOND
INTO :WS-DB2-TIMESTAMP
FROM SYSIBM.SYSDUMMY1
Sorry, Looks like I didnt phrase my question properly.
SQL-TIMESTAMP-1 is the first part of consistency token which gets generated during pre-compile and I believe is used to match the token with load module during program execution.
I used to think that if a program has multiple sql queries then db2 will verify the token only for the 1st query it encounters during the execution of the program and not for every query which the progarm has.
Or if the program program hits the same query multiple times(like in PERFORM in loop) then db2 will verify the token only once during the execution of the program.
To check this I wrote this test program and changed the 1st part of consistency token using ADD statement.
So when my program ran, during 1st call of 1000- para the query executed with sqlcode 000 as expected. After the ADD statement(which changed the consistency token during the execution), 1000- para was called again and this time the same query returned sqlcode -805. That means every time the query is encountered, db2 verifies the token if it is matching with load module or not. So why does db2 verifies token every time in the same instance of program run?
This is the sample, I will share the compile listing later on.
Code:
01 SQL-PLIST1.
05 SQL-PLIST-CON PIC S9(9) COMP-4 VALUE +2654208.
05 SQL-CALLTYPE PIC S9(4) COMP-4 VALUE +30.
05 SQL-PROG-NAME PIC X(8) VALUE 'BV174V02'.
05 SQL-TIMESTAMP-1 PIC S9(9) COMP-4 VALUE +368527768.
05 SQL-TIMESTAMP-2 PIC S9(9) COMP-4 VALUE +507831332.
*----------------------------------------------------------------*
PROCEDURE DIVISION.
*----------------------------------------------------------------*
0000-MAINLINE.
DISPLAY 'BEFORE 1ST CALL TO 1000- PARA'
PERFORM 1000-TEST
THRU 1000-EXIT
DISPLAY 'AFTER 1ST CALL TO 1000- PARA'
ADD +1 TO SQL-TIMESTAMP-1
DISPLAY 'BEFORE 2ND CALL TO 1000- PARA'
PERFORM 1000-TEST
THRU 1000-EXIT
DISPLAY 'AFTER 2ND CALL TO 1000- PARA'
GOBACK
.
0000-EXIT.
EXIT.
1000-TEST.
DISPLAY 'INSIDE 1000- PARA'
EXEC SQL
SELECT CURRENT_TIMESTAMP
INTO :WS-DB2-TIMESTAMP
FROM SYSIBM.SYSDUMMY1
END-EXEC
MOVE SQLCODE TO WS-SQLCODE
EVALUATE WS-SQLCODE
WHEN 0
DISPLAY 'WS-SQLCODE :' WS-SQLCODE
WHEN +100
DISPLAY 'ROW NOT FOUND'
WHEN OTHER
DISPLAY 'WS-SQLCODE : ' WS-SQLCODE
END-EVALUATE
.
1000-EXIT.
EXIT.
********************** Bottom of Data **************
Compile listing
ID DIVISION.
PROGRAM-ID. TBYTES02.
ENVIRONMENT DIVISION.
DATA DIVISION.
WORKING-STORAGE SECTION.
*----------------------------------------------------------------*
01 SQL-VERS.
05 SQL-VERS-PREF PIC X(04) VALUE 'VER.'.
05 SQL-VERS-DATA PIC X(64)
VALUE '2018-04-26-07.17.01.354294'.
77 SQL-TEMP PIC X(128).
77 DSN-TEMP PIC S9(9) COMP-5.
77 DSN-TMP2 PIC S9(18) COMP-3.
77 DSNNROWS PIC S9(9) COMP-5.
77 DSNNTYPE PIC S9(4) COMP-5.
77 DSNNLEN PIC S9(4) COMP-5.
77 SQL-NULL PIC S9(9) COMP-5 VALUE +0.
77 SQL-INIT-FLAG PIC S9(4) COMP-5 VALUE +0.
88 SQL-INIT-DONE VALUE +1.
77 SQL-FILE-READ PIC S9(9) COMP-5 VALUE +2.
77 SQL-FILE-CREATE PIC S9(9) COMP-5 VALUE +8.
77 SQL-FILE-OVERWRITE PIC S9(9) COMP-5 VALUE +16.
77 SQL-FILE-APPEND PIC S9(9) COMP-5 VALUE +32.
01 SQL-PLIST1.
05 SQL-PLIST-CON PIC S9(9) COMP-5 VALUE +4195328.
05 SQL-CALLTYPE PIC S9(4) COMP-5 VALUE +30.
05 SQL-PROG-NAME PIC X(8) VALUE X'54425954XXXXXX'.
05 SQL-TIMESTAMP-1 PIC S9(9) COMP-5 VALUE +445093610.
05 SQL-TIMESTAMP-2 PIC S9(9) COMP-5 VALUE +240602501.
05 SQL-SECTION PIC S9(4) COMP-5 VALUE +1.
05 SQL-CODEPTR PIC S9(9) COMP-5.
05 SQL-VPARMPTR PIC S9(9) COMP-5 VALUE +0.
05 SQL-APARMPTR PIC S9(9) COMP-5 VALUE +0.
05 FILLER PIC S9(4) COMP-5 VALUE +1208.
05 SQL-STMT-TYPE PIC S9(4) COMP-5 VALUE +231.
05 SQL-STMT-NUM PIC S9(9) COMP-5 VALUE +41.
05 SQL-PLIST-FLG PIC S9(4) COMP-5 VALUE +0.
05 FILLER PIC X(18) VALUE
X'000000000000000000000000000000000000'.
05 SQL-AVAR-LIST1.
10 PRE-SQLDAID PIC X(8) VALUE 'SQLDA ,'.
10 PRE-SQLDABC PIC S9(9) COMP-5 VALUE +60.
10 PRE-SQLN PIC S9(4) COMP-5 VALUE +1.
10 PRE-SQLLD PIC S9(4) COMP-5 VALUE +1.
10 PRE-SQLVAR.
12 SQLVAR-BASE1.
15 SQL-AVAR-TYPE1 PIC S9(4) COMP-5 VALUE +452.
15 SQL-AVAR-LEN1 PIC S9(4) COMP-5 VALUE +26.
15 SQL-AVAR-ADDRS1.
20 SQL-AVAR-ADDR1 PIC S9(9) COMP-5.
20 SQL-AVAR-IND1 PIC S9(9) COMP-5.
15 SQL-AVAR-NAME1.
20 SQL-AVAR-NAMEL1 PIC S9(4) COMP-5 VALUE +0.
20 SQL-AVAR-NAMEC1 PIC X(30) VALUE ' '.
PROCEDURE DIVISION.
*----------------------------------------------------------------*
DSNSQL SECTION.
SQL-SKIP.
GO TO SQL-INIT-END.
SQL-INITIAL.
MOVE 1 TO SQL-INIT-FLAG.
CALL 'DSNHADDR' USING SQL-APARMPTR OF SQL-PLIST1 SQL-AVAR-LIS
- T1.
CALL 'DSNHADD2' USING SQL-AVAR-ADDRS1 IN
SQL-AVAR-LIST1 WS-DB2-TIMESTAMP SQL-NULL.
CALL 'DSNHADDR' USING SQL-CODEPTR OF SQL-PLIST1 SQLCA.
SQL-INIT-END.
CONTINUE.
0000-MAINLINE.
DISPLAY 'BEFORE 1ST CALL TO 1000- PARA'
PERFORM 1000-TEST
THRU 1000-EXIT
DISPLAY 'AFTER 1ST CALL TO 1000- PARA'
ADD +1 TO SQL-TIMESTAMP-1
DISPLAY 'BEFORE 2ND CALL TO 1000- PARA'
PERFORM 1000-TEST
THRU 1000-EXIT
DISPLAY 'AFTER 2ND CALL TO 1000- PARA'
GOBACK
.
0000-EXIT.
EXIT.
1000-TEST.
DISPLAY 'INSIDE 1000- PARA'
*****EXEC SQL
***** SELECT CURRENT_TIMESTAMP
***** INTO :WS-DB2-TIMESTAMP
***** FROM SYSIBM.SYSDUMMY1
*****END-EXEC
PERFORM SQL-INITIAL UNTIL SQL-INIT-DONE
CALL 'DSNHLI2' USING SQL-PLIST1
MOVE SQLCODE TO WS-SQLCODE
High order digit positions in the sender may be truncated in the move t
receiver "WS-SQLCODE (BINARY INTEGER)".
EVALUATE WS-SQLCODE
WHEN 0
DISPLAY 'WS-SQLCODE :' WS-SQLCODE
WHEN +100
DISPLAY 'ROW NOT FOUND'
WHEN OTHER
DISPLAY 'WS-SQLCODE : ' WS-SQLCODE
END-EVALUATE
.
1000-EXIT.
EXIT.
Joined: 26 Nov 2002 Posts: 12388 Topics: 75 Location: San Jose
Posted: Fri Apr 27, 2018 12:09 pm Post subject:
rsantosh wrote:
Hello Kolusu,
Sorry, Looks like I didnt phrase my question properly.
SQL-TIMESTAMP-1 is the first part of consistency token which gets generated during pre-compile and I believe is used to match the token with load module during program execution.
I used to think that if a program has multiple sql queries then db2 will verify the token only for the 1st query it encounters during the execution of the program and not for every query which the progarm has.
Or if the program program hits the same query multiple times(like in PERFORM in loop) then db2 will verify the token only once during the execution of the program.
To check this I wrote this test program and changed the 1st part of consistency token using ADD statement.
So when my program ran, during 1st call of 1000- para the query executed with sqlcode 000 as expected. After the ADD statement(which changed the consistency token during the execution), 1000- para was called again and this time the same query returned sqlcode -805. That means every time the query is encountered, db2 verifies the token if it is matching with load module or not. So why does db2 verifies token every time in the same instance of program run?
Rsanthosh,
I am not sure as to why you are messing with Consistency tokens, but every execution is treated as if it is separate transaction. DB2 does NOT keep track of your programs or SQL it executed before.
The SQL precompiler(the first step in preparing an application program) places timestamp 'y' in the DBRM, and time stamp 'x' in the parameter list in the application program for each SQL statement. At BIND time, DB2 stores the DBRM timestamp for run-time use. At run-time, timestamp 'x',for the SQL statement being processed, is compared with timestamp 'y' derived from the DBRM 'z' at BIND time.If the two timestamps do not match then you will get errors like -805 or -818.
Think of a simple scenario. You are given a key to enter a room. Once you enter and exit out of it, then you modify the same key slightly. You cannot use the same key to get back in. The room does not keep track that you entered a minute ago.
If you really want to get fancy and change the consistency token for every SQL execution , you can try to update the column CONTOKEN in SYSIBM.SYSPACKAGE table and then run your individual queries. _________________ Kolusu
www.linkedin.com/in/kolusu
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
