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".CSGCS B54 - 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".CSGCS B54 - Calling the stored procedure.

"SYSPROC".CSGCS B54 - Run completed.

But not others:

SYSPROC".ALRT2S 91 - 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".ALRT2 S91 - Exception occurred while running:

null

"SYSPROC".ALRT2 S91 - Roll back completed successfully.

"SYSPROC".ALRT2 S91 - 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.too ls.dev.dc.svc.m akers.GenSP390R unner:runFailed ^Exception
anException = java.lang.NullP ointerException ^^ ^ entry ^ runIt

2006-09-19 16:03:18.323 ^
com.ibm.db2.too ls.dev.dc.svc.m akers.GenSP390R unner:getFullQu alifyName^RLRou tine
rtn = ALRTMS01^^ ^ entry ^ runIt

2006-09-19 16:03:18.323 ^
com.ibm.db2.too ls.dev.dc.util. Utility:toUpper Case^String aString =
ALRTMS01^^ ^ entry ^ runIt

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

2006-09-19 16:03:18.323 ^
com.ibm.db2.too ls.dev.dc.svc.m akers.GenSP390R unner:getFullQu alifyName^RLRou tine
rtn = ALRTMS01^^ ^ return("SYSPROC ".ALRTMS01) ^ runIt

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

null^^ ^ entry ^ runIt

2006-09-19 16:03:18.323 ^
com.ibm.db2.too ls.dev.dc.util. message.DCMsg:D CMsg^String anActionID =
RUN^ Object anObject = ALRTMS01^ int aStatusFlag = 25^ String aMsg =
"SYSPROC".ALRTM S01 - 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, requiredUniqueI nstance: null,
renderedString: null, typeEnum: CHARACTER, formatterClassN ame: null,
keyable: true, whereable: true) (characterSet: 800, length: 32)
(characterSetBy teSize: UNDEFINED, encodingScheme: null)^^ ^ entry ^
Thread-13

2006-09-19 16:03:12.714 ^
com.ibm.db2.too ls.dev.dc.cm.mo del.ModelUtil:g etCopy^RDBMembe rType aMT =
com.ibm.etools. rdbschema.impl. DB2OS390Charact erStringTypeImp l@33838e8
(externalName: null, name: null, defaultValue: null, jdbcEnumType:
null) (domainType: DB2UDBOS390_V7, requiredUniqueI nstance: null,
renderedString: null, typeEnum: CHARACTER, formatterClassN ame: null,
keyable: true, whereable: true) (characterSet: 800, length: 32)
(characterSetBy teSize: 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".CSGCS B54 - 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".CSGCS B54 - Calling the stored procedure.

"SYSPROC".CSGCS B54 - Run completed.

But not others:

SYSPROC".ALRT2S 91 - 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".ALRT2 S91 - Exception occurred while running:

null

"SYSPROC".ALRT2 S91 - Roll back completed successfully.

"SYSPROC".ALRT2 S91 - 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.c om 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.too ls.dev.dc.svc.m akers.GenSP390R unner:runFailed ^Exception
anException = java.lang.NullP ointerException ^^ ^ entry ^ runIt

2006-09-19 16:03:18.323 ^
com.ibm.db2.too ls.dev.dc.svc.m akers.GenSP390R unner:getFullQu alifyName^RLRou tine
rtn = ALRTMS01^^ ^ entry ^ runIt

2006-09-19 16:03:18.323 ^
com.ibm.db2.too ls.dev.dc.util. Utility:toUpper Case^String aString =
ALRTMS01^^ ^ entry ^ runIt

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

2006-09-19 16:03:18.323 ^
com.ibm.db2.too ls.dev.dc.svc.m akers.GenSP390R unner:getFullQu alifyName^RLRou tine
rtn = ALRTMS01^^ ^ return("SYSPROC ".ALRTMS01) ^ runIt

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

null^^ ^ entry ^ runIt

2006-09-19 16:03:18.323 ^
com.ibm.db2.too ls.dev.dc.util. message.DCMsg:D CMsg^String anActionID =
RUN^ Object anObject = ALRTMS01^ int aStatusFlag = 25^ String aMsg =
"SYSPROC".ALRTM S01 - 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, requiredUniqueI nstance: null,
renderedString: null, typeEnum: CHARACTER, formatterClassN ame: null,
keyable: true, whereable: true) (characterSet: 800, length: 32)
(characterSetBy teSize: UNDEFINED, encodingScheme: null)^^ ^ entry ^
Thread-13

2006-09-19 16:03:12.714 ^
com.ibm.db2.too ls.dev.dc.cm.mo del.ModelUtil:g etCopy^RDBMembe rType aMT =
com.ibm.etools. rdbschema.impl. DB2OS390Charact erStringTypeImp l@33838e8
(externalName: null, name: null, defaultValue: null, jdbcEnumType:
null) (domainType: DB2UDBOS390_V7, requiredUniqueI nstance: null,
renderedString: null, typeEnum: CHARACTER, formatterClassN ame: null,
keyable: true, whereable: true) (characterSet: 800, length: 32)
(characterSetBy teSize: 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".CSGC SB54 - 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".CSGC SB54 - Calling the stored procedure.

"SYSPROC".CSGC SB54 - Run completed.

But not others:

SYSPROC".ALRT2 S91 - 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".ALRT 2S91 - Exception occurred while running:

null

"SYSPROC".ALRT 2S91 - Roll back completed successfully.

"SYSPROC".ALRT 2S91 - 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.c om 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.too ls.dev.dc.svc.m akers.GenSP390R unner:runFailed ^Exception
anException = java.lang.NullP ointerException ^^ ^ entry ^ runIt

2006-09-19 16:03:18.323 ^
com.ibm.db2.too ls.dev.dc.svc.m akers.GenSP390R unner:getFullQu alifyName^RLRou tine
rtn = ALRTMS01^^ ^ entry ^ runIt

2006-09-19 16:03:18.323 ^
com.ibm.db2.too ls.dev.dc.util. Utility:toUpper Case^String aString =
ALRTMS01^^ ^ entry ^ runIt

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

2006-09-19 16:03:18.323 ^
com.ibm.db2.too ls.dev.dc.svc.m akers.GenSP390R unner:getFullQu alifyName^RLRou tine
rtn = ALRTMS01^^ ^ return("SYSPROC ".ALRTMS01) ^ runIt

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

null^^ ^ entry ^ runIt

2006-09-19 16:03:18.323 ^
com.ibm.db2.too ls.dev.dc.util. message.DCMsg:D CMsg^String anActionID =
RUN^ Object anObject = ALRTMS01^ int aStatusFlag = 25^ String aMsg =
"SYSPROC".ALRTM S01 - 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, requiredUniqueI nstance: null,
renderedString: null, typeEnum: CHARACTER, formatterClassN ame: null,
keyable: true, whereable: true) (characterSet: 800, length: 32)
(characterSetBy teSize: UNDEFINED, encodingScheme: null)^^ ^ entry ^
Thread-13

2006-09-19 16:03:12.714 ^
com.ibm.db2.too ls.dev.dc.cm.mo del.ModelUtil:g etCopy^RDBMembe rType aMT =
com.ibm.etools. rdbschema.impl. DB2OS390Charact erStringTypeImp l@33838e8
(externalName: null, name: null, defaultValue: null, jdbcEnumType:
null) (domainType: DB2UDBOS390_V7, requiredUniqueI nstance: null,
renderedString: null, typeEnum: CHARACTER, formatterClassN ame: null,
keyable: true, whereable: true) (characterSet: 800, length: 32)
(characterSetBy teSize: 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".CSGCS B54 - 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".CSGCS B54 - Calling the stored procedure.

"SYSPROC".CSGCS B54 - Run completed.

But not others:

SYSPROC".ALRT2S 91 - 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".ALRT2 S91 - Exception occurred while running:

null

"SYSPROC".ALRT2 S91 - Roll back completed successfully.

"SYSPROC".ALRT2 S91 - 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.ALRTMSD 2 (
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.ALRTMSD 2 TO PUBLIC;

CREATE PROCEDURE SYSPROC.ALRTMSD 3 (
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.ALRTMSD 3 TO PUBLIC;

Thanks,

Nishi

ni*****@gmail.c om 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.c om 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.too ls.dev.dc.svc.m akers.GenSP390R unner:runFailed ^Exception
anException = java.lang.NullP ointerException ^^ ^ entry ^ runIt
>
2006-09-19 16:03:18.323 ^
com.ibm.db2.too ls.dev.dc.svc.m akers.GenSP390R unner:getFullQu alifyName^RLRou tine
rtn = ALRTMS01^^ ^ entry ^ runIt
>
2006-09-19 16:03:18.323 ^
com.ibm.db2.too ls.dev.dc.util. Utility:toUpper Case^String aString =
ALRTMS01^^ ^ entry ^ runIt
>
2006-09-19 16:03:18.323 ^
com.ibm.db2.too ls.dev.dc.util. Utility:toUpper Case^String aString =
ALRTMS01^^ ^ return(ALRTMS01 ) ^ runIt
>
2006-09-19 16:03:18.323 ^
com.ibm.db2.too ls.dev.dc.svc.m akers.GenSP390R unner:getFullQu alifyName^RLRou tine
rtn = ALRTMS01^^ ^ return("SYSPROC ".ALRTMS01) ^ runIt
>
2006-09-19 16:03:18.323 ^
com.ibm.db2.too ls.dev.dc.util. message.DCMsg:D CMsg^String anActionID =
RUN^ Object anObject = ALRTMS01^ int aStatusFlag = 25^ String aMsg =
"SYSPROC".ALRTM S01 - Exception occurred while running:
>
null^^ ^ entry ^ runIt
>
2006-09-19 16:03:18.323 ^
com.ibm.db2.too ls.dev.dc.util. message.DCMsg:D CMsg^String anActionID =
RUN^ Object anObject = ALRTMS01^ int aStatusFlag = 25^ String aMsg =
"SYSPROC".ALRTM S01 - 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, requiredUniqueI nstance: null,
renderedString: null, typeEnum: CHARACTER, formatterClassN ame: null,
keyable: true, whereable: true) (characterSet: 800, length: 32)
(characterSetBy teSize: UNDEFINED, encodingScheme: null)^^ ^ entry ^
Thread-13
>
2006-09-19 16:03:12.714 ^
com.ibm.db2.too ls.dev.dc.cm.mo del.ModelUtil:g etCopy^RDBMembe rType aMT =
com.ibm.etools. rdbschema.impl. DB2OS390Charact erStringTypeImp l@33838e8
(externalName: null, name: null, defaultValue: null, jdbcEnumType:
null) (domainType: DB2UDBOS390_V7, requiredUniqueI nstance: null,
renderedString: null, typeEnum: CHARACTER, formatterClassN ame: null,
keyable: true, whereable: true) (characterSet: 800, length: 32)
(characterSetBy teSize: 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".CSGC SB54 - 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".CSGC SB54 - Calling the stored procedure.
>>
>"SYSPROC".CSGC SB54 - Run completed.
>>
>But not others:
>>
>SYSPROC".ALRT2 S91 - 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".ALRT 2S91 - Exception occurred while running:
>>
>null
>>
>"SYSPROC".ALRT 2S91 - Roll back completed successfully.
>>
>"SYSPROC".ALRT 2S91 - 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.
>