Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Commiting the table after closing the cursor

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Commiting the table after closing the cursor
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    Post subject:
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    Post subject: Reply to: Commiting the table after closing the cursor
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    Post subject: Reply to: Commiting the table after closing the cursor
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    Post subject:
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    Post subject: Reply to: Commiting the table after closing the cursor
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    Post subject:
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    Post subject:
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    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 Insert a Row_number into table useit DB2 2 Tue Sep 19, 2017 1:07 pm
No new posts how to see when the last read access ... Mike 1304 DB2 1 Tue Sep 12, 2017 7:52 pm
No new posts PC (UTF-8) -> z/OS (EBCDIC) -> ... prino All Other Mainframe Topics 4 Fri Sep 01, 2017 1:47 am
No new posts Adding a userid to SMTP Security table Yolanda Harvey JCL & VSAM 1 Sun Aug 13, 2017 6:16 pm
No new posts Problem commiting to DB2 from IMS MPP Ole Soerensen IMS DB/DC 2 Wed Jul 26, 2017 5:19 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us