issue in Store procedure or Cognos

jaspal · New User Joined: 22 May 2007 Posts: 68 Location: mumbai

Hi Forum,
We generates cognos report by fetching data from DB2 database using Cobol DB2 Store procedures.
when cognos fires a query by passing parameters to store procedure it returns result set back to cognos and report is generated.
During this process we face the following error on clicking some of links:
'AN error occurred while performing operation 'SqlopenResult'='-28'.

However when we again click the same links after sometime the reports is generated successfully.
*****
We looked at the APPTUNE log and found potential application issues with the stored procedures?

N8TRSSP1 generated the following SQL error
SQL Error +------------- Data from Last SQL Error ------------+
Code DB2 Details Date and Time User Plan Program
------ ---- -------- -------------------------- -------- -------- --------
+ -601 DB3P 10 2008-07-02-07.49.07.23379 XX5671A DISTSERV N8TRSSP1
Apptune captures on 17 consecutive error details.
List of each -601
* -601 DB3P 2008-07-02-07.49.07.23379 XX5671A DISTSERV N8TRSSP1
* -601 DB3P 2008-07-02-07.48.55.46232 XX5671A DISTSERV N8TRSSP1
* -601 DB3P 2008-07-02-07.48.55.45298 XX5671A DISTSERV N8TRSSP1
* -601 DB3P 2008-07-02-07.48.55.44752 XX5671A DISTSERV N8TRSSP1
* -601 DB3P 2008-07-02-07.43.34.76837 XX5671A DISTSERV N8TRSSP1
* -601 DB3P 2008-07-02-07.43.34.76262 XX5671A DISTSERV N8TRSSP1
* -601 DB3P 2008-07-02-04.54.35.03450 XX5671A DISTSERV N8TRSSP1

-601 THE NAME OF THE OBJECT TO BE CREATED OR THE TARGET OF A RENAME
STATEMENT IS IDENTICAL TO THE EXISTING NAME name OF THE OBJECT TYPE
obj-type

N8TRSSP2 generated the following SQL error

SQL Error +------------- Data from Last SQL Error ------------+
Code DB2 Details Date and Time User Plan Program
------ ---- -------- -------------------------- -------- -------- --------
-601 DB3P 6 2008-07-02-07.46.59.01610 XX5671A DISTSERV N8TRSSP2
Apptune captures on 17 consecutive error details.
List of each -601
-601 DB3P 2008-07-02-07.46.59.01610 XX5671A DISTSERV N8TRSSP2
-601 DB3P 2008-07-02-07.46.59.01496 XX5671A DISTSERV N8TRSSP2
-601 DB3P 2008-07-02-07.44.59.43994 XX5671A DISTSERV N8TRSSP2
-601 DB3P 2008-07-02-07.44.59.43422 XX5671A DISTSERV N8TRSSP2
-601 DB3P 2008-07-02-05.29.20.36895 XX5671A DISTSERV N8TRSSP2
-601 DB3P 2008-07-02-05.28.56.42215 XX5671A DISTSERV N8TRSSP2

-601 THE NAME OF THE OBJECT TO BE CREATED OR THE TARGET OF A RENAME
STATEMENT IS IDENTICAL TO THE EXISTING NAME name OF THE OBJECT TYPE
obj-type
N8TRSSP3 generated the following SQL errors

-204 DB3P 3 2008-07-02-07.47.26.54746 XX5671A DISTSERV N8TRSSP3
Apptune captures on 17 consecutive error details.
List of each -204
-204 DB3P 2008-07-02-07.47.26.54746 XX5671A DISTSERV N8TRSSP3
-204 DB3P 2008-07-02-05.31.59.12147 XX5671A DISTSERV N8TRSSP3
-204 DB3P 2008-07-02-05.29.42.01025 XX5671A DISTSERV N8TRSSP3

-601 DB3P 2 2008-07-02-04.57.02.09836 XX5671A DISTSERV N8TRSSP3

-204 name IS AN UNDEFINED NAME

Explanation: The object identified by name is not defined in the DB2
subsystem. This SQLCODE can be generated for any type of DB2 object.

-601 THE NAME OF THE OBJECT TO BE CREATED OR THE TARGET OF A RENAME
STATEMENT IS IDENTICAL TO THE EXISTING NAME name OF THE OBJECT TYPE
obj-type

just want to know do any once face this problem and do any one have any solution.

Regards,
Jaspal
+919833814470.

jaspal · New User Joined: 22 May 2007 Posts: 68 Location: mumbai

Just want to update :

We are passing parameters to SP from cognos.

there are few queries that fetch data from DB2 tables and insert in GTT table and result set is sent back to cognos.

Regards,
Jaspal

ascelepius · New User Joined: 16 Jun 2008 Posts: 35 Location: bangalore

I guess this should be due to your Global Temporary Tables. If your tables are declared tables, then these need to be dropped at the end of the transaction that created these temp tables or need to be dropped and recreated at the time of initiation of a new transaction by a stored procedure.

regards,
asci(i)

jaspal · New User Joined: 22 May 2007 Posts: 68 Location: mumbai

when session expires GTT drops it self.

however in our case when we again query the SP from cognos ...it is giving -601 sqlcode while declaring.

i just want to know can we drop a table every time in initlization para before decalaring when we call store procedure......
by doing this it will drop the table if it is present.

ascelepius · New User Joined: 16 Jun 2008 Posts: 35 Location: bangalore

I donot think that your temp tables are dropped at the end of your transaction. Always drop your session tables if you do not need to query from them repeatedly during a transaction/running a report.

If this cant be done you can either (try) dropping your tables at the start of your transaction(and ignore the -204 error), or you can check for the existence of the temp table and then create this if its not present(either ways leads to some ugly programming

)

regards,
asci(i)

ashimer · Posted: Thu Jul 03, 2008 9:06 pm

GTT's are dropped at the end of your transaction ... You might be getting -601 because you are entering the SP more than once within the same transaction and encountering a GTT declaration ... drop the GTT at the start of the SP and declare a condition for sqlstate '42704' (object exists) and '42710' object not present and ignore thse conditions (if it is a db2 sp ) ... or else handle -204 and -601 if its cobol SP ...

jaspal · New User Joined: 22 May 2007 Posts: 68 Location: mumbai

we just click a HTML report which have link that passes some parameters to Cobol db2 Stored procedure.

which does following activities:
1). declare GTT tables
Checks return code - if -601
- drop the table
check return code - if +0
declare again table
2). fetch data from different tables using cursor and insert into GTT tables then resut set is send back to cognos for display on HTML report.

hence session is over.
------
if i would generate report again it some time it gives error displayed.
and if we again click the link again after somr time the report is generated.

Solution :

i am modifying my code to drop a session.* ( session name ) in initilization para and evaluate following return codes in SP :
if SQLCODE = '-601' or '+0' or '-204'
go ahead and declare table
evaluate +601
and processing.

else
abend.

Regards,
Jaspal singh virk
9833814470