SQL901 when updating Timestamp field in RPG
Hi All,
We are using an OS/400 application that predominantly runs on COBOL/400 programs. One of our performance intensive program is being converted into a service program model. In this model, we have removed all DB2 queries that were within the COBOL programs and have docked them into RPG programs. The RPG programs are part of a service program. All the COBOL and RPG programs are created under a single activation group.
This is a basic flow of how our programs now run:
Run CL à Calls COBOL program à Call to Service program when DB2 queries are to be executed (DB2 Queries are docked into RPG programs)
The processing of the program runs fine for most part of the program. We however receive an SQL 901 when trying to execute the following query:
PP#PYM025#3000A B EXPORT
DP#PYM025#3000A PI
DIN_CHAR_VAR1 LIKE(BSM_VAR1) CONST
DIN_CHAR_VAR2 LIKE(BSM_VAR2) CONST
DIN_CHAR_VAR3 LIKE(BSM_VAR3) CONST
DIN_CHAR_VAR4 LIKE(BSM_VAR4) CONST
DIN_CHAR_VAR5 10A CONST
DIN_DATETIMESTAMP_VAR6 LIKE(BSM_VAR6) CONST
DSQLCOD LIKE(SQLCODE)
C/EXEC SQL
C+ UPDATE TABLE_TEST
C+ SET CAHR_FIELD_A = :IN_CHAR_VAR4,
C+ DATETIMESTAMP_FIELD_A = :IN_DATETIMESTAMP_VAR6,
C+ CHAR_FIELD_B = :IN_CHAR_VAR5,
C+ DATETIMESTAMP_FIELD_B = :IN_DATETIMESTAMP_VAR6
C+ WHERE (CHAR_FIELD_C = :IN_CHAR_VAR1)
C+ AND (CHAR_FIELD_D = :IN_CHAR_VAR2)
C+ AND (CHAR_FIELD_E = :IN_CHAR_VAR3)
C/END-EXEC
C EVAL SQLCOD = SQLCODE
The RPG which contains this query was complied with *MOD option and date and time formats as *ISO.
The F Spec of the table is declared as:
FTABLE_TESTIF E K DISK RENAME(TABLE_TEST:TABLETES
F PREFIX(BSM_) INFDS(DS_SUM)
Also, there is a trigger defined on the table TABLE_TEST. The trigger creates a record into the TABLE_TEST_HISTORY table when there is an Update or a Delete performed on the TABLE_TEST.
Can anyone please advise on what could be causing the SQL901 and how do we overcome this?
Thanks & Regards
Ali Bhagat
We are using an OS/400 application that predominantly runs on COBOL/400 programs. One of our performance intensive program is being converted into a service program model. In this model, we have removed all DB2 queries that were within the COBOL programs and have docked them into RPG programs. The RPG programs are part of a service program. All the COBOL and RPG programs are created under a single activation group.
This is a basic flow of how our programs now run:
Run CL à Calls COBOL program à Call to Service program when DB2 queries are to be executed (DB2 Queries are docked into RPG programs)
The processing of the program runs fine for most part of the program. We however receive an SQL 901 when trying to execute the following query:
PP#PYM025#3000A B EXPORT
DP#PYM025#3000A PI
DIN_CHAR_VAR1 LIKE(BSM_VAR1) CONST
DIN_CHAR_VAR2 LIKE(BSM_VAR2) CONST
DIN_CHAR_VAR3 LIKE(BSM_VAR3) CONST
DIN_CHAR_VAR4 LIKE(BSM_VAR4) CONST
DIN_CHAR_VAR5 10A CONST
DIN_DATETIMESTAMP_VAR6 LIKE(BSM_VAR6) CONST
DSQLCOD LIKE(SQLCODE)
C/EXEC SQL
C+ UPDATE TABLE_TEST
C+ SET CAHR_FIELD_A = :IN_CHAR_VAR4,
C+ DATETIMESTAMP_FIELD_A = :IN_DATETIMESTAMP_VAR6,
C+ CHAR_FIELD_B = :IN_CHAR_VAR5,
C+ DATETIMESTAMP_FIELD_B = :IN_DATETIMESTAMP_VAR6
C+ WHERE (CHAR_FIELD_C = :IN_CHAR_VAR1)
C+ AND (CHAR_FIELD_D = :IN_CHAR_VAR2)
C+ AND (CHAR_FIELD_E = :IN_CHAR_VAR3)
C/END-EXEC
C EVAL SQLCOD = SQLCODE
The RPG which contains this query was complied with *MOD option and date and time formats as *ISO.
The F Spec of the table is declared as:
FTABLE_TESTIF E K DISK RENAME(TABLE_TEST:TABLETES
F PREFIX(BSM_) INFDS(DS_SUM)
Also, there is a trigger defined on the table TABLE_TEST. The trigger creates a record into the TABLE_TEST_HISTORY table when there is an Update or a Delete performed on the TABLE_TEST.
Can anyone please advise on what could be causing the SQL901 and how do we overcome this?
Thanks & Regards
Ali Bhagat
PP#PYM025#3000A B EXPORT
DP#PYM025#3000A PI
DIN_CHAR_VAR1 LIKE(BSM_VAR1) CONST
DIN_CHAR_VAR2 LIKE(BSM_VAR2) CONST
DIN_CHAR_VAR3 LIKE(BSM_VAR3) CONST
DIN_CHAR_VAR4 LIKE(BSM_VAR4) CONST
DIN_CHAR_VAR5 10A CONST
DIN_DATETIMESTAMP_VAR6 LIKE(BSM_VAR6) CONST
DSQLCOD LIKE(SQLCODE)
C/EXEC SQL
C+ UPDATE TABLE_TEST
C+ SET CAHR_FIELD_A = :IN_CHAR_VAR4,
C+ DATETIMESTAMP_FIELD_A = :IN_DATETIMESTAMP_VAR6,
C+ CHAR_FIELD_B = :IN_CHAR_VAR5,
C+ DATETIMESTAMP_FIELD_B = :IN_DATETIMESTAMP_VAR6
C+ WHERE (CHAR_FIELD_C = :IN_CHAR_VAR1)
C+ AND (CHAR_FIELD_D = :IN_CHAR_VAR2)
C+ AND (CHAR_FIELD_E = :IN_CHAR_VAR3)
C/END-EXEC
C EVAL SQLCOD = SQLCODE
In line 11, is "CAHR_FIELD_A" spelled correctly?
- Gary Patterson
- Gary Patterson
ASKER
Hi Gary,
Thanks for your response.
I had to manually change the actual field names to CHAR_FIELD_A etc (company policy :)). All field names in the actual code are correct because the program compiles succesfully.
The SQL error message is SQL0901. Basically we get a SQLCODE -901 when trying to execute the query above.
Message . . . . : SQL system error.
Cause . . . . . : An SQL system error has occurred. The current SQL statement cannot be completed successfully.
From the joblogs I can see that there are some "Pointer not set for location referenced" errors before the actual "SQL system error" shows up.
Any pointers?
Regards
Ali Bhagat
Thanks for your response.
I had to manually change the actual field names to CHAR_FIELD_A etc (company policy :)). All field names in the actual code are correct because the program compiles succesfully.
The SQL error message is SQL0901. Basically we get a SQLCODE -901 when trying to execute the query above.
Message . . . . : SQL system error.
Cause . . . . . : An SQL system error has occurred. The current SQL statement cannot be completed successfully.
From the joblogs I can see that there are some "Pointer not set for location referenced" errors before the actual "SQL system error" shows up.
Any pointers?
Regards
Ali Bhagat
Just to make absolutely sure we are on the same page: In line 11 above, it looks like CHAR is misspelled "CAHR" - the "H" and "A" are transposed. Before we get into more complex troubleshooting, please verify that this problem does not exist in the real code. A misspelled field here would certainly cause problems at runtime.
SQL is syntax-checked at compile time, but table existence, field existence etc. is not checked. I can compile that query on my system even though the underlying table doesn't even exist.
Most likely the problem is just the misspelled field name that we see in the query above.
- Gary Patterson
SQL is syntax-checked at compile time, but table existence, field existence etc. is not checked. I can compile that query on my system even though the underlying table doesn't even exist.
Most likely the problem is just the misspelled field name that we see in the query above.
- Gary Patterson
ASKER
Hi Gary,
This is how the query looks in the actual code. The CAHR was a typo.
C+ SET CHAR_FIELD_A = :IN_CHAR_VAR4,
C+ DATETIMESTAMP_FIELD_A = :IN_DATETIMESTAMP_VAR6,
C+ CHAR_FIELD_B = :IN_CHAR_VAR5,
C+ DATETIMESTAMP_FIELD_B = :IN_DATETIMESTAMP_VAR6
C+ WHERE (CHAR_FIELD_C = :IN_CHAR_VAR1)
C+ AND (CHAR_FIELD_D = :IN_CHAR_VAR2)
C+ AND (CHAR_FIELD_E = :IN_CHAR_VAR3)
Thanks for your time.
Regards
Ali Bhagat
This is how the query looks in the actual code. The CAHR was a typo.
C+ SET CHAR_FIELD_A = :IN_CHAR_VAR4,
C+ DATETIMESTAMP_FIELD_A = :IN_DATETIMESTAMP_VAR6,
C+ CHAR_FIELD_B = :IN_CHAR_VAR5,
C+ DATETIMESTAMP_FIELD_B = :IN_DATETIMESTAMP_VAR6
C+ WHERE (CHAR_FIELD_C = :IN_CHAR_VAR1)
C+ AND (CHAR_FIELD_D = :IN_CHAR_VAR2)
C+ AND (CHAR_FIELD_E = :IN_CHAR_VAR3)
Thanks for your time.
Regards
Ali Bhagat
PP#PYM025#3000A B EXPORT
DP#PYM025#3000A PI
DIN_CHAR_VAR1 LIKE(BSM_VAR1) CONST
DIN_CHAR_VAR2 LIKE(BSM_VAR2) CONST
DIN_CHAR_VAR3 LIKE(BSM_VAR3) CONST
DIN_CHAR_VAR4 LIKE(BSM_VAR4) CONST
DIN_CHAR_VAR5 10A CONST
DIN_DATETIMESTAMP_VAR6 LIKE(BSM_VAR6) CONST
DSQLCOD LIKE(SQLCODE)
C/EXEC SQL
C+ UPDATE TABLE_TEST
C+ SET CHAR_FIELD_A = :IN_CHAR_VAR4,
C+ DATETIMESTAMP_FIELD_A = :IN_DATETIMESTAMP_VAR6,
C+ CHAR_FIELD_B = :IN_CHAR_VAR5,
C+ DATETIMESTAMP_FIELD_B = :IN_DATETIMESTAMP_VAR6
C+ WHERE (CHAR_FIELD_C = :IN_CHAR_VAR1)
C+ AND (CHAR_FIELD_D = :IN_CHAR_VAR2)
C+ AND (CHAR_FIELD_E = :IN_CHAR_VAR3)
C/END-EXEC
C EVAL SQLCOD = SQLCODE
OK, now that we've eliminated that, post a joblog, and show the detailed error message text from all of the errors you see up to the SQL0901. Some thoughts:
The "Pointer not set for location recerenced" often indicates a parameter passing problem, or a BASED variable that is not set to a valid storage location.
Do this when you get a 901:
IF SQLCOD =-901
DUMP(A)
ENDIF
And inspect your INxxxxxxxxx host variables. One or more of them is probably not set in the calling program.
- Gary Patterson
The "Pointer not set for location recerenced" often indicates a parameter passing problem, or a BASED variable that is not set to a valid storage location.
Do this when you get a 901:
IF SQLCOD =-901
DUMP(A)
ENDIF
And inspect your INxxxxxxxxx host variables. One or more of them is probably not set in the calling program.
- Gary Patterson
bhagatali:
Two minor comments just for completeness:
Also, you have a parameter named "SQLCOD". You should avoid host variables with names that begin "SQL". Better to use OUT_SQLCOD or a similar name.
Tom
Two minor comments just for completeness:
DIN_DATETIMESTAMP_VAR6 LIKE(BSM_VAR6) CONST
DSQLCOD LIKE(SQLCODE)
C EVAL SQLCOD = SQLCODE
You show IN_DATETIMESTAMP_VAR6 defined as LIKE(BSM_VAR6). Could you show how BSM_VAR6 is defined?DSQLCOD LIKE(SQLCODE)
C EVAL SQLCOD = SQLCODE
Also, you have a parameter named "SQLCOD". You should avoid host variables with names that begin "SQL". Better to use OUT_SQLCOD or a similar name.
Tom
SQL0901 means:
An SQL system error has occurred. The current SQL statement cannot be completed successfully. The error will not prevent other SQL statements from being processed. Previous messages may indicate that there is a problem with the SQL statement and SQL did not correctly diagnose the error.
SO there must be an other message that tells you what is (was) wrong)
To view the messages, use the DSPJOBLOG command if running interactively, or the WRKJOB command to view the output of a precompile. An application program receiving this return code may attempt further SQL statements.
Regards,
Murph
An SQL system error has occurred. The current SQL statement cannot be completed successfully. The error will not prevent other SQL statements from being processed. Previous messages may indicate that there is a problem with the SQL statement and SQL did not correctly diagnose the error.
SO there must be an other message that tells you what is (was) wrong)
To view the messages, use the DSPJOBLOG command if running interactively, or the WRKJOB command to view the output of a precompile. An application program receiving this return code may attempt further SQL statements.
Regards,
Murph
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thanks for posting back with the resolution.
- Gary Patterson
- Gary Patterson
Please reproduce the error, and post the detailed error message here, including first and second level text.
- Gary Patterson