Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

issue in Store procedure or Cognos

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
jaspal

New User


Joined: 22 May 2007
Posts: 68
Location: mumbai

PostPosted: Thu Jul 03, 2008 11:11 am    Post subject: issue in Store procedure or Cognos
Reply with quote

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.
Back to top
View user's profile Send private message

jaspal

New User


Joined: 22 May 2007
Posts: 68
Location: mumbai

PostPosted: Thu Jul 03, 2008 11:14 am    Post subject: Re: issue in Store procedure or Cognos
Reply with quote

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
Back to top
View user's profile Send private message
ascelepius

New User


Joined: 16 Jun 2008
Posts: 35
Location: bangalore

PostPosted: Thu Jul 03, 2008 12:40 pm    Post subject: Reply to: issue in Store procedure or Cognos
Reply with quote

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)
Back to top
View user's profile Send private message
jaspal

New User


Joined: 22 May 2007
Posts: 68
Location: mumbai

PostPosted: Thu Jul 03, 2008 8:04 pm    Post subject: Re: Reply to: issue in Store procedure or Cognos
Reply with quote

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.
Back to top
View user's profile Send private message
ascelepius

New User


Joined: 16 Jun 2008
Posts: 35
Location: bangalore

PostPosted: Thu Jul 03, 2008 8:22 pm    Post subject: Reply to: issue in Store procedure or Cognos
Reply with quote

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 icon_rolleyes.gif )

regards,
asci(i)
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Thu Jul 03, 2008 9:06 pm    Post subject:
Reply with quote

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 ...
Back to top
View user's profile Send private message
jaspal

New User


Joined: 22 May 2007
Posts: 68
Location: mumbai

PostPosted: Thu Jul 03, 2008 11:06 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Calling procedure with multiple entri... steve-myers PL/I & Assembler 5 Fri Jan 27, 2017 3:33 pm
No new posts Execessive parameter issue Sumeendar JCL & VSAM 5 Mon Dec 19, 2016 4:35 pm
No new posts Regarding COBOL Stored Procedure opti... selvamsrinivasan85 DB2 4 Fri Nov 04, 2016 8:57 pm
No new posts DFHRESPONSE returns issue divated CICS 3 Wed Nov 02, 2016 6:32 pm
No new posts Access SQLCA fields inside a stored p... gangapd DB2 6 Thu Oct 27, 2016 10:20 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us