Problem relating to running Stored Procedures

[nishi57]

I hope I can get some help regarding this issue, which has been going
on for a while. I have a desktop user who is having problem running
"Stored Procedures". The DB2 Connect application works fine but when he
runs the stored procedure, he gets the following error message.

"SYSPROC".CSGCSB54 - Run started.

Data returned in result sets is limited to the first 100 rows.

Data returned in result set columns is limited to the first 20 bytes or
characters.

"SYSPROC".CSGCSB54 - Calling the stored procedure.

"SYSPROC".CSGCSB54 - Run completed.

But not others:

SYSPROC".ALRT2S91 - Run started.

Data returned in result sets is limited to the first 100 rows.

Data returned in result set columns is limited to the first 20 bytes or
characters.

"SYSPROC".ALRT2S91 - Exception occurred while running:

null

"SYSPROC".ALRT2S91 - Roll back completed successfully.

"SYSPROC".ALRT2S91 - Run failed.

The ALRT stored procedures are the ones he is running, do you know why
it's doing this. The user is using the latest version of DB2 v9.

It seems with the null response that DB2 does not know what to do with
the query. Is there some kind of table that has the allowed versions of
software to execute against DB2?

Any help will be greatly appreciated.

Thankx.

[nishi57]

Here is some more information I could get on this issue, here are
traces for a successful run and an unsuccessful run. Please help me
trace the problem.

Thanks.

com.ibm.db2.tools.dev.dc.svc.makers.GenSP390Runner :runFailed^Exception
anException = java.lang.NullPointerException^^ ^ entry ^ runIt

2006-09-19 16:03:18.323 ^
com.ibm.db2.tools.dev.dc.svc.makers.GenSP390Runner :getFullQualifyName^RLRoutine
rtn = ALRTMS01^^ ^ entry ^ runIt

2006-09-19 16:03:18.323 ^
com.ibm.db2.tools.dev.dc.util.Utility:toUpperCase^ String aString =
ALRTMS01^^ ^ entry ^ runIt

2006-09-19 16:03:18.323 ^
com.ibm.db2.tools.dev.dc.util.Utility:toUpperCase^ String aString =
ALRTMS01^^ ^ return(ALRTMS01) ^ runIt

2006-09-19 16:03:18.323 ^
com.ibm.db2.tools.dev.dc.svc.makers.GenSP390Runner :getFullQualifyName^RLRoutine
rtn = ALRTMS01^^ ^ return("SYSPROC".ALRTMS01) ^ runIt

2006-09-19 16:03:18.323 ^
com.ibm.db2.tools.dev.dc.util.message.DCMsg:DCMsg^ String anActionID =
RUN^ Object anObject = ALRTMS01^ int aStatusFlag = 25^ String aMsg =
"SYSPROC".ALRTMS01 - Exception occurred while running:

null^^ ^ entry ^ runIt

2006-09-19 16:03:18.323 ^
com.ibm.db2.tools.dev.dc.util.message.DCMsg:DCMsg^ String anActionID =
RUN^ Object anObject = ALRTMS01^ int aStatusFlag = 25^ String aMsg =
"SYSPROC".ALRTMS01 - Exception occurred while running:

null^^ ^ return() ^ runIt

2006-09-19 16:03:18.323 ^

and DomainType :

(externalName: null, name: null, defaultValue: null, jdbcEnumType:
null) (domainType: DB2UDBOS390_V7, requiredUniqueInstance: null,
renderedString: null, typeEnum: CHARACTER, formatterClassName: null,
keyable: true, whereable: true) (characterSet: 800, length: 32)
(characterSetByteSize: UNDEFINED, encodingScheme: null)^^ ^ entry ^
Thread-13

2006-09-19 16:03:12.714 ^
com.ibm.db2.tools.dev.dc.cm.model.ModelUtil:getCop y^RDBMemberType aMT =
com.ibm.etools.rdbschema.impl.DB2OS390CharacterStr ingTypeImpl@33838e8
(externalName: null, name: null, defaultValue: null, jdbcEnumType:
null) (domainType: DB2UDBOS390_V7, requiredUniqueInstance: null,
renderedString: null, typeEnum: CHARACTER, formatterClassName: null,
keyable: true, whereable: true) (characterSet: 800, length: 32)
(characterSetByteSize: UNDEFINED, encodingScheme: null)^^ ^

**************************end********************* ************************************************** ************************************************** **************************

I hope I can get some help regarding this issue, which has been going
on for a while. I have a desktop user who is having problem running
"Stored Procedures". The DB2 Connect application works fine but when he
runs the stored procedure, he gets the following error message.

"SYSPROC".CSGCSB54 - Run started.

Data returned in result sets is limited to the first 100 rows.

Data returned in result set columns is limited to the first 20 bytes or
characters.

"SYSPROC".CSGCSB54 - Calling the stored procedure.

"SYSPROC".CSGCSB54 - Run completed.

But not others:

SYSPROC".ALRT2S91 - Run started.

Data returned in result sets is limited to the first 100 rows.

Data returned in result set columns is limited to the first 20 bytes or
characters.

"SYSPROC".ALRT2S91 - Exception occurred while running:

null

"SYSPROC".ALRT2S91 - Roll back completed successfully.

"SYSPROC".ALRT2S91 - Run failed.

The ALRT stored procedures are the ones he is running, do you know why
it's doing this. The user is using the latest version of DB2 v9.

It seems with the null response that DB2 does not know what to do with
the query. Is there some kind of table that has the allowed versions of
software to execute against DB2?

Any help will be greatly appreciated.

Thankx.

[Phil Sherman]

This may be an application problem in the ALRT... stored procedure. It
looks like the retrieval returned a null (column) value that caused a
Java exception when it was processed through Java's string handling
routines. This isn't an uncommon problem when an application programmer
doesn't fully understand (or wasn't shown) the complete table definition.

There are four possible fixes:
1. Change the application code to check for and appropriately handle the
null column value.
2. Change the SQL statement to use a COALESCE (or VALUE) function to
provide a default value when the column contains nulls.
3. Use a try ... catch code block to catch the exception and handle it
there.
4. Scrub the data to assign default values for all rows. If this is the
choice, you need to discover why the original table definition didn't
include the NOT NULL [WITH DEFAULT] clause[s].

Phil Sherman

ni*****@gmail.com wrote:

Here is some more information I could get on this issue, here are
traces for a successful run and an unsuccessful run. Please help me
trace the problem.

Thanks.

com.ibm.db2.tools.dev.dc.svc.makers.GenSP390Runner :runFailed^Exception
anException = java.lang.NullPointerException^^ ^ entry ^ runIt

2006-09-19 16:03:18.323 ^
com.ibm.db2.tools.dev.dc.svc.makers.GenSP390Runner :getFullQualifyName^RLRoutine
rtn = ALRTMS01^^ ^ entry ^ runIt

2006-09-19 16:03:18.323 ^
com.ibm.db2.tools.dev.dc.util.Utility:toUpperCase^ String aString =
ALRTMS01^^ ^ entry ^ runIt

2006-09-19 16:03:18.323 ^
com.ibm.db2.tools.dev.dc.util.Utility:toUpperCase^ String aString =
ALRTMS01^^ ^ return(ALRTMS01) ^ runIt

2006-09-19 16:03:18.323 ^
com.ibm.db2.tools.dev.dc.svc.makers.GenSP390Runner :getFullQualifyName^RLRoutine
rtn = ALRTMS01^^ ^ return("SYSPROC".ALRTMS01) ^ runIt

2006-09-19 16:03:18.323 ^
com.ibm.db2.tools.dev.dc.util.message.DCMsg:DCMsg^ String anActionID =
RUN^ Object anObject = ALRTMS01^ int aStatusFlag = 25^ String aMsg =
"SYSPROC".ALRTMS01 - Exception occurred while running:

null^^ ^ entry ^ runIt

2006-09-19 16:03:18.323 ^
com.ibm.db2.tools.dev.dc.util.message.DCMsg:DCMsg^ String anActionID =
RUN^ Object anObject = ALRTMS01^ int aStatusFlag = 25^ String aMsg =
"SYSPROC".ALRTMS01 - Exception occurred while running:

null^^ ^ return() ^ runIt

2006-09-19 16:03:18.323 ^

and DomainType :

(externalName: null, name: null, defaultValue: null, jdbcEnumType:
null) (domainType: DB2UDBOS390_V7, requiredUniqueInstance: null,
renderedString: null, typeEnum: CHARACTER, formatterClassName: null,
keyable: true, whereable: true) (characterSet: 800, length: 32)
(characterSetByteSize: UNDEFINED, encodingScheme: null)^^ ^ entry ^
Thread-13

2006-09-19 16:03:12.714 ^
com.ibm.db2.tools.dev.dc.cm.model.ModelUtil:getCop y^RDBMemberType aMT =
com.ibm.etools.rdbschema.impl.DB2OS390CharacterStr ingTypeImpl@33838e8
(externalName: null, name: null, defaultValue: null, jdbcEnumType:
null) (domainType: DB2UDBOS390_V7, requiredUniqueInstance: null,
renderedString: null, typeEnum: CHARACTER, formatterClassName: null,
keyable: true, whereable: true) (characterSet: 800, length: 32)
(characterSetByteSize: UNDEFINED, encodingScheme: null)^^ ^

**************************end********************* ************************************************** ************************************************** **************************

>I hope I can get some help regarding this issue, which has been going
on for a while. I have a desktop user who is having problem running
"Stored Procedures". The DB2 Connect application works fine but when he
runs the stored procedure, he gets the following error message.

"SYSPROC".CSGCSB54 - Run started.

Data returned in result sets is limited to the first 100 rows.

Data returned in result set columns is limited to the first 20 bytes or
characters.

"SYSPROC".CSGCSB54 - Calling the stored procedure.

"SYSPROC".CSGCSB54 - Run completed.

But not others:

SYSPROC".ALRT2S91 - Run started.

Data returned in result sets is limited to the first 100 rows.

Data returned in result set columns is limited to the first 20 bytes or
characters.

"SYSPROC".ALRT2S91 - Exception occurred while running:

null

"SYSPROC".ALRT2S91 - Roll back completed successfully.

"SYSPROC".ALRT2S91 - Run failed.

The ALRT stored procedures are the ones he is running, do you know why
it's doing this. The user is using the latest version of DB2 v9.

It seems with the null response that DB2 does not know what to do with
the query. Is there some kind of table that has the allowed versions of
software to execute against DB2?

Any help will be greatly appreciated.

Thankx.

[nishi57]

Thanks for your response Phil, I will try this and let you know the
results.

Nishi

Phil Sherman wrote:

This may be an application problem in the ALRT... stored procedure. It
looks like the retrieval returned a null (column) value that caused a
Java exception when it was processed through Java's string handling
routines. This isn't an uncommon problem when an application programmer
doesn't fully understand (or wasn't shown) the complete table definition.

There are four possible fixes:
1. Change the application code to check for and appropriately handle the
null column value.
2. Change the SQL statement to use a COALESCE (or VALUE) function to
provide a default value when the column contains nulls.
3. Use a try ... catch code block to catch the exception and handle it
there.
4. Scrub the data to assign default values for all rows. If this is the
choice, you need to discover why the original table definition didn't
include the NOT NULL [WITH DEFAULT] clause[s].

Phil Sherman

ni*****@gmail.com wrote:

Here is some more information I could get on this issue, here are
traces for a successful run and an unsuccessful run. Please help me
trace the problem.

Thanks.

com.ibm.db2.tools.dev.dc.svc.makers.GenSP390Runner :runFailed^Exception
anException = java.lang.NullPointerException^^ ^ entry ^ runIt

2006-09-19 16:03:18.323 ^
com.ibm.db2.tools.dev.dc.svc.makers.GenSP390Runner :getFullQualifyName^RLRoutine
rtn = ALRTMS01^^ ^ entry ^ runIt

2006-09-19 16:03:18.323 ^
com.ibm.db2.tools.dev.dc.util.Utility:toUpperCase^ String aString =
ALRTMS01^^ ^ entry ^ runIt

2006-09-19 16:03:18.323 ^
com.ibm.db2.tools.dev.dc.util.Utility:toUpperCase^ String aString =
ALRTMS01^^ ^ return(ALRTMS01) ^ runIt

2006-09-19 16:03:18.323 ^
com.ibm.db2.tools.dev.dc.svc.makers.GenSP390Runner :getFullQualifyName^RLRoutine
rtn = ALRTMS01^^ ^ return("SYSPROC".ALRTMS01) ^ runIt

2006-09-19 16:03:18.323 ^
com.ibm.db2.tools.dev.dc.util.message.DCMsg:DCMsg^ String anActionID =
RUN^ Object anObject = ALRTMS01^ int aStatusFlag = 25^ String aMsg =
"SYSPROC".ALRTMS01 - Exception occurred while running:

null^^ ^ entry ^ runIt

2006-09-19 16:03:18.323 ^
com.ibm.db2.tools.dev.dc.util.message.DCMsg:DCMsg^ String anActionID =
RUN^ Object anObject = ALRTMS01^ int aStatusFlag = 25^ String aMsg =
"SYSPROC".ALRTMS01 - Exception occurred while running:

null^^ ^ return() ^ runIt

2006-09-19 16:03:18.323 ^

and DomainType :

(externalName: null, name: null, defaultValue: null, jdbcEnumType:
null) (domainType: DB2UDBOS390_V7, requiredUniqueInstance: null,
renderedString: null, typeEnum: CHARACTER, formatterClassName: null,
keyable: true, whereable: true) (characterSet: 800, length: 32)
(characterSetByteSize: UNDEFINED, encodingScheme: null)^^ ^ entry ^
Thread-13

2006-09-19 16:03:12.714 ^
com.ibm.db2.tools.dev.dc.cm.model.ModelUtil:getCop y^RDBMemberType aMT =
com.ibm.etools.rdbschema.impl.DB2OS390CharacterStr ingTypeImpl@33838e8
(externalName: null, name: null, defaultValue: null, jdbcEnumType:
null) (domainType: DB2UDBOS390_V7, requiredUniqueInstance: null,
renderedString: null, typeEnum: CHARACTER, formatterClassName: null,
keyable: true, whereable: true) (characterSet: 800, length: 32)
(characterSetByteSize: UNDEFINED, encodingScheme: null)^^ ^

**************************end********************* ************************************************** ************************************************** **************************

I hope I can get some help regarding this issue, which has been going
on for a while. I have a desktop user who is having problem running
"Stored Procedures". The DB2 Connect application works fine but when he
runs the stored procedure, he gets the following error message.

"SYSPROC".CSGCSB54 - Run started.

Data returned in result sets is limited to the first 100 rows.

Data returned in result set columns is limited to the first 20 bytes or
characters.

"SYSPROC".CSGCSB54 - Calling the stored procedure.

"SYSPROC".CSGCSB54 - Run completed.

But not others:

SYSPROC".ALRT2S91 - Run started.

Data returned in result sets is limited to the first 100 rows.

Data returned in result set columns is limited to the first 20 bytes or
characters.

"SYSPROC".ALRT2S91 - Exception occurred while running:

null

"SYSPROC".ALRT2S91 - Roll back completed successfully.

"SYSPROC".ALRT2S91 - Run failed.

The ALRT stored procedures are the ones he is running, do you know why
it's doing this. The user is using the latest version of DB2 v9.

It seems with the null response that DB2 does not know what to do with
the query. Is there some kind of table that has the allowed versions of
software to execute against DB2?

Any help will be greatly appreciated.

Thankx.

[nishi57]

Do you think it might be the way the stored procedure is defined that
is causing this issue. I have a stored procedure that works in DB2
Connect V 8.1.9; e.g. ALRTMSD3, and 1 that does not; e.g. ALRTMSD2? Do
let me know if anyone can find any solution to this -

CREATE PROCEDURE SYSPROC.ALRTMSD2 (
IN CHAR(8) FOR SBCS DATA CCSID EBCDIC
, OUT VARCHAR(31980) FOR SBCS DATA CCSID EBCDIC
, OUT CHAR(177) FOR SBCS DATA CCSID EBCDIC
)
COLLID SDSN3CLST
EXTERNAL LANGUAGE COBOL
DYNAMIC RESULT SET 0
NOT DETERMINISTIC
FENCED
CALLED ON NULL INPUT
PARAMETER STYLE DB2SQL
MODIFIES SQL DATA
NO DBINFO
STAY RESIDENT NO
ASUTIME NO LIMIT
WLM ENVIRONMENT DSN3WLM1
PROGRAM TYPE MAIN
SECURITY DB2
COMMIT ON RETURN NO
;
GRANT EXECUTE ON PROCEDURE SYSPROC.ALRTMSD2 TO PUBLIC;

CREATE PROCEDURE SYSPROC.ALRTMSD3 (
IN INPUT_PARM1 CHAR(2) FOR SBCS DATA CCSID EBCDIC
, OUT OUTPUT_PARM1 VARCHAR(1250) FOR SBCS DATA CCSID EBCDIC
, OUT OUTPUT_PARM2 CHAR(177) FOR SBCS DATA CCSID EBCDIC
, OUT OUTPUT_PARM3 VARCHAR(240) FOR SBCS DATA CCSID EBCDIC
)
COLLID SDSN3CLST
EXTERNAL LANGUAGE COBOL
DYNAMIC RESULT SET 0
NOT DETERMINISTIC
FENCED
CALLED ON NULL INPUT
PARAMETER STYLE DB2SQL
MODIFIES SQL DATA
NO DBINFO
STAY RESIDENT NO
ASUTIME LIMIT 98160
WLM ENVIRONMENT DSN3WLM
PROGRAM TYPE MAIN
SECURITY DB2
COMMIT ON RETURN NO
;
GRANT EXECUTE ON PROCEDURE SYSPROC.ALRTMSD3 TO PUBLIC;

Thanks,

Nishi

ni*****@gmail.com wrote:

Thanks for your response Phil, I will try this and let you know the
results.

Nishi

Phil Sherman wrote:

This may be an application problem in the ALRT... stored procedure. It
looks like the retrieval returned a null (column) value that caused a
Java exception when it was processed through Java's string handling
routines. This isn't an uncommon problem when an application programmer
doesn't fully understand (or wasn't shown) the complete table definition.

There are four possible fixes:
1. Change the application code to check for and appropriately handle the
null column value.
2. Change the SQL statement to use a COALESCE (or VALUE) function to
provide a default value when the column contains nulls.
3. Use a try ... catch code block to catch the exception and handle it
there.
4. Scrub the data to assign default values for all rows. If this is the
choice, you need to discover why the original table definition didn't
include the NOT NULL [WITH DEFAULT] clause[s].

Phil Sherman

ni*****@gmail.com wrote:

Here is some more information I could get on this issue, here are
traces for a successful run and an unsuccessful run. Please help me
trace the problem.
>
Thanks.
>
com.ibm.db2.tools.dev.dc.svc.makers.GenSP390Runner :runFailed^Exception
anException = java.lang.NullPointerException^^ ^ entry ^ runIt
>
2006-09-19 16:03:18.323 ^
com.ibm.db2.tools.dev.dc.svc.makers.GenSP390Runner :getFullQualifyName^RLRoutine
rtn = ALRTMS01^^ ^ entry ^ runIt
>
2006-09-19 16:03:18.323 ^
com.ibm.db2.tools.dev.dc.util.Utility:toUpperCase^ String aString =
ALRTMS01^^ ^ entry ^ runIt
>
2006-09-19 16:03:18.323 ^
com.ibm.db2.tools.dev.dc.util.Utility:toUpperCase^ String aString =
ALRTMS01^^ ^ return(ALRTMS01) ^ runIt
>
2006-09-19 16:03:18.323 ^
com.ibm.db2.tools.dev.dc.svc.makers.GenSP390Runner :getFullQualifyName^RLRoutine
rtn = ALRTMS01^^ ^ return("SYSPROC".ALRTMS01) ^ runIt
>
2006-09-19 16:03:18.323 ^
com.ibm.db2.tools.dev.dc.util.message.DCMsg:DCMsg^ String anActionID =
RUN^ Object anObject = ALRTMS01^ int aStatusFlag = 25^ String aMsg =
"SYSPROC".ALRTMS01 - Exception occurred while running:
>
null^^ ^ entry ^ runIt
>
2006-09-19 16:03:18.323 ^
com.ibm.db2.tools.dev.dc.util.message.DCMsg:DCMsg^ String anActionID =
RUN^ Object anObject = ALRTMS01^ int aStatusFlag = 25^ String aMsg =
"SYSPROC".ALRTMS01 - Exception occurred while running:
>
null^^ ^ return() ^ runIt
>
2006-09-19 16:03:18.323 ^
>
and DomainType :
>
(externalName: null, name: null, defaultValue: null, jdbcEnumType:
null) (domainType: DB2UDBOS390_V7, requiredUniqueInstance: null,
renderedString: null, typeEnum: CHARACTER, formatterClassName: null,
keyable: true, whereable: true) (characterSet: 800, length: 32)
(characterSetByteSize: UNDEFINED, encodingScheme: null)^^ ^ entry ^
Thread-13
>
2006-09-19 16:03:12.714 ^
com.ibm.db2.tools.dev.dc.cm.model.ModelUtil:getCop y^RDBMemberType aMT =
com.ibm.etools.rdbschema.impl.DB2OS390CharacterStr ingTypeImpl@33838e8
(externalName: null, name: null, defaultValue: null, jdbcEnumType:
null) (domainType: DB2UDBOS390_V7, requiredUniqueInstance: null,
renderedString: null, typeEnum: CHARACTER, formatterClassName: null,
keyable: true, whereable: true) (characterSet: 800, length: 32)
(characterSetByteSize: UNDEFINED, encodingScheme: null)^^ ^
>
**************************end********************* ************************************************** ************************************************** **************************
>
>
>
>
>I hope I can get some help regarding this issue, which has been going
>on for a while. I have a desktop user who is having problem running
>"Stored Procedures". The DB2 Connect application works fine but when he
>runs the stored procedure, he gets the following error message.
>>
>"SYSPROC".CSGCSB54 - Run started.
>>
>Data returned in result sets is limited to the first 100 rows.
>>
>Data returned in result set columns is limited to the first 20 bytes or
>characters.
>>
>"SYSPROC".CSGCSB54 - Calling the stored procedure.
>>
>"SYSPROC".CSGCSB54 - Run completed.
>>
>But not others:
>>
>SYSPROC".ALRT2S91 - Run started.
>>
>Data returned in result sets is limited to the first 100 rows.
>>
>Data returned in result set columns is limited to the first 20 bytes or
>characters.
>>
>"SYSPROC".ALRT2S91 - Exception occurred while running:
>>
>null
>>
>"SYSPROC".ALRT2S91 - Roll back completed successfully.
>>
>"SYSPROC".ALRT2S91 - Run failed.
>>
>The ALRT stored procedures are the ones he is running, do you know why
>it's doing this. The user is using the latest version of DB2 v9.
>>
>It seems with the null response that DB2 does not know what to do with
>the query. Is there some kind of table that has the allowed versions of
>software to execute against DB2?
>>
>Any help will be greatly appreciated.
>>
>Thankx.
>