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
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.
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.
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 )
Joined: 13 Feb 2004 Posts: 360 Location: Bangalore
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 ...
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.
)