IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Commiting the table after closing the cursor
IBM Mainframe Forums -> DB2
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
avanti84
Currently Banned
New User


Joined: 10 Sep 2008
Posts: 5
Location: Pune
PostPosted: Tue Jan 06, 2009 4:01 pm
 Reply with quote
Hi All,

I am calling one stored proc(SP1) from other stored proc (SP2) for one date.Then I am fetching the record from SP1 temp table and inserting each row fetched into SP2.
After all the records fetched for one date,I am closing SP1 cursor and commiting.(So both SP1 temp table and SP2 temp table will be commited?)

Then I call SP1 for secod date and repeat the same process.
But I am getting empty resultset.

What should I do?

When I remove the commit,I am getting the lowest dated data.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
PostPosted: Tue Jan 06, 2009 5:30 pm
 Reply with quote
How is your temp table declared ??? ON COMMIT PRESERVE ROWS ???
Back to top
View user's profile Send private message
avanti84
Currently Banned
New User


Joined: 10 Sep 2008
Posts: 5
Location: Pune
PostPosted: Tue Jan 06, 2009 6:47 pm
 Reply with quote
I don't know.But I have declared cursor for the table with hold with return for.
Back to top
View user's profile Send private message
avanti84
Currently Banned
New User


Joined: 10 Sep 2008
Posts: 5
Location: Pune
PostPosted: Tue Jan 06, 2009 6:51 pm
 Reply with quote
Which of following parameters gives the above metioned property?
NAME
CREATOR
TYPE
DBNAME
TSNAME
DBID
OBID
COLCOUNT
EDPROC
VALPROC
CLUSTERTYPE
CLUSTERRID
CARD
NPAGES
PCTPAGES
IBMREQD
REMARKS
PARENTS
CHILDREN
KEYCOLUMNS
RECLENGTH
STATUS
KEYOBID
LABEL
CHECKFLAG
CHECKRID
AUDITING
CREATEDBY
LOCATION
TBCREATOR
TBNAME
CREATEDTS
ALTEREDTS
DATACAPTURE
RBA1
RBA2
PCTROWCOMP
STATSTIME
CHECKS
CARDF
CHECKRID5B
ENCODING_SCHEME
TABLESTATUS
NPAGESF
SPACEF
AVGROWLEN
RELCREATED
NUM_DEP_MQTS
VERSION
PARTKEYCOLNUM
SPLIT_ROWS
SECURITY_LABEL
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
PostPosted: Tue Jan 06, 2009 7:01 pm
 Reply with quote
declare your temp table with ON COMMIT PRESERVER ROWS ...this will prevent loss of data from the session table when you commit ....
Contact your seniors for more info on this ...
Back to top
View user's profile Send private message
avanti84
Currently Banned
New User


Joined: 10 Sep 2008
Posts: 5
Location: Pune
PostPosted: Tue Jan 06, 2009 7:08 pm
 Reply with quote
Temporary table has been created by DBA and not by me.So I don't have access to change the properties.

When I run the first time count of the record inserted is suppose 'N' then for second time,count again starts from 1 and not from N+1.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
PostPosted: Tue Jan 06, 2009 7:12 pm
 Reply with quote
Avanti,
Here what exactly did you mean by a temp table ?

post the declaration of the table where you are inserting your records in your program ...
Back to top
View user's profile Send private message
avanti84
Currently Banned
New User


Joined: 10 Sep 2008
Posts: 5
Location: Pune
PostPosted: Tue Jan 06, 2009 7:28 pm
 Reply with quote
We are call sub-sp multiplpe time in stored proc.

INSERT INTO IRIWSTM2
( SEQ_NB
, RECORD_TYPE
, UNIVERSAL_USER_ID
, CUST_SHRT_NM
, AS_OF_DT
, NXT_GOOD_BUS_DT
, CNTRY_SWIFT_CD
, CNTRY_NM
, BANK_ID
, BRANCH_ID
, BANK_BRANCH_NM
, CURRENCY_CD
, CURRENCY_NM
, CRCY_DEC_LOC_NB
, ACCOUNT_ID
, RPT_ACCT_NB
, RPT_ACCT_NM
, NO_INFO_CD
, UPDT_STS_CD
, LAST_ACCS_VIEW_DT
, LAST_ACCS_VIEW_TM
, BAL_UPDT_DT
, BAL_UPDT_TM
, BAL_PST_CD
, ITEM_COUNT
, LEDGER_AMT
, DAY0_AVAIL_AMT
, DAY1_AVAIL_AMT
, DAY2_AVAIL_AMT
, DAY2P_AVAIL_AMT
, DAY3P_AVAIL_AMT
, UNKWN_AVAIL_AMT
, BAI_TYPE_CD
, VALUE_DT
, FUNDS_TYPE_CD
, TXN_DR_CR_CD
, BANK_REF_CD
, CUST_REF_CD
, SHORT_DESC_TX
, PROD_GROUP_CD
, PROD_GROUP_TX
, TXN_AS_OF_DT
, TXN_NARR_TX
, ADDM_TX
, ADDM_SEQ_NO
, REPAIR_IN
, REVERSAL_IN
, OVERRIDE_IN
, TXN_STS_CD
, FED_RULE_13_IN
, CTRY_RPT_SQNO
, CURR_RPT_SQNO
, BNK_RPT_SQNO
, ACCT_RPT_SQNO
, CUST_REF_SORT_NB
)
VALUES
( :STM2-SEQ-NB
, :STM2-RECORD-TYPE
, :STM2-UNIVERSAL-USER-ID
, :STM2-CUST-SHRT-NM :IND-STM2-CUST-SHRT-NM
, :STM2-AS-OF-DT
, :STM2-NXT-GOOD-BUS-DT :IND-STM2-NXT-GOOD-BUS-DT
, :STM2-CNTRY-SWIFT-CD
, :STM2-CNTRY-NM
, :STM2-BANK-ID
, :STM2-BRANCH-ID
, :STM2-BANK-BRANCH-NM
, :STM2-CURRENCY-CD
, :STM2-CURRENCY-NM
, :STM2-CRCY-DEC-LOC-NB
, :STM2-ACCOUNT-ID :IND-STM2-ACCOUNT-ID
, :STM2-RPT-ACCT-NB :IND-STM2-RPT-ACCT-NB
, :STM2-RPT-ACCT-NM :IND-STM2-RPT-ACCT-NM
, :STM2-NO-INFO-CD
, :STM2-UPDT-STS-CD
, :STM2-ITEM-COUNT :IND-STM2-ITEM-COUNT
, :STM2-LEDGER-AMT :IND-STM2-LEDGER-AMT
, :STM2-DAY0-AVAIL-AMT :IND-STM2-DAY0-AVAIL-AMT
, :STM2-DAY1-AVAIL-AMT :IND-STM2-DAY1-AVAIL-AMT
, :STM2-DAY2-AVAIL-AMT :IND-STM2-DAY2-AVAIL-AMT
, :STM2-DAY2P-AVAIL-AMT :IND-STM2-DAY2P-AVAIL-AMT
, :STM2-DAY3P-AVAIL-AMT :IND-STM2-DAY3P-AVAIL-AMT
, :STM2-UNKWN-AVAIL-AMT :IND-STM2-UNKWN-AVAIL-AMT
, :STM2-BAI-TYPE-CD :IND-STM2-BAI-TYPE-CD
, :STM2-VALUE-DT :IND-STM2-VALUE-DT
, :STM2-FUNDS-TYPE-CD :IND-STM2-FUNDS-TYPE-CD
, :STM2-TXN-DR-CR-CD :IND-STM2-TXN-DR-CR-CD
, :STM2-BANK-REF-CD :IND-STM2-BANK-REF-CD
, :STM2-CUST-REF-CD :IND-STM2-CUST-REF-CD
, :STM2-SHORT-DESC-TX :IND-STM2-SHORT-DESC-TX
, :STM2-PROD-GROUP-CD :IND-STM2-PROD-GROUP-CD
, :STM2-PROD-GROUP-TX :IND-STM2-PROD-GROUP-TX
, :STM2-TXN-AS-OF-DT :IND-STM2-TXN-AS-OF-DT
, :STM2-TXN-NARR-TX :IND-STM2-TXN-NARR-TX
, :STM2-ADDM-TX :IND-STM2-ADDM-TX
, :STM2-ADDM-SEQ-NO :IND-STM2-ADDM-SEQ-NO
, :STM2-REPAIR-IN :IND-STM2-REPAIR-IN
, :STM2-REVERSAL-IN :IND-STM2-REVERSAL-IN
, :STM2-OVERRIDE-IN :IND-STM2-OVERRIDE-IN
, :STM2-TXN-STS-CD :IND-STM2-TXN-STS-CD
, :STM2-FED-RULE-13-IN :IND-STM2-FED-RULE-13-IN
, :STM2-CTRY-RPT-SQNO :IND-STM2-CTRY-RPT-SQNO
, :STM2-CURR-RPT-SQNO :IND-STM2-CURR-RPT-SQNO
, :STM2-BNK-RPT-SQNO :IND-STM2-BNK-RPT-SQNO
, :STM2-ACCT-RPT-SQNO :IND-STM2-ACCT-RPT-SQNO
, :STM2-CUST-REF-SORT-NB :IND-STM2-CUST-REF-SORT-NB
)

This is what is our insert statement and I tried declaring cursor with hold with return for.But still I am getting blank resultset after commit.

If commit is removed,data is displayed only for last date fetched.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 

Similar Topics
Topic Forum Replies
No new posts Load new table with Old unload - DB2 DB2 6
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Multiple table unload using INZUTILB DB2 2
No new posts Check data with Exception Table DB2 0
No new posts Dynamically pass table name to a sele... DB2 2
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us