View previous topic :: View next topic |
Author |
Message |
Magesh_J Intermediate
![](images/avatars/gallery/members/Sqr.jpg)
Joined: 21 Jun 2014 Posts: 259 Topics: 54
|
Posted: Mon Sep 10, 2018 11:50 am Post subject: Join local temporary table and remote table in Cobol db2. |
|
|
Hi,
I am trying to write a COBOL db2 program accessing temporary table and remote table.
Code: |
EXEC SQL
DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP
(A_NO CHAR(10) NOT NULL)
ON COMMIT PRESERVE ROWS
END-EXEC.
EXEC SQL
DECLARE R_CURSOR CURSOR FOR
SELECT R.ACC_NO
FROM
SESSION.TEMP R
INNER JOIN
S_A A
ON R.A_NO = A.A_NUM
INNER JOIN
S_A_CON AC
ON AC.A_ID = A.A_ID
INNER JOIN
S_CON C
ON C.P_ROW_ID = AC.C_ID
WHERE
AC.R_T_CD = 'OBLIGOR'
AND AC.RIB_35 IS NOT NULL
AND AC.RIB_35 != ''
END-EXEC.
|
Code: |
SQL ERROR ON OPENING CURSOR R_CURSOR
ERROR IN DSNTIAR!!
SQLCA = SQLCA h
56023
DSNT408I SQLCODE = -512, ERROR: STATEMENT REFERENCE TO REMOTE OBJECT IS INVALI
DSNT418I SQLSTATE = 56023 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXOTDA SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -300 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFED4' X'00000000' X'00000000' X'FFFFFFFF' X'000
INFORMATION
|
Bind is successful, but while running the job it is failing with the above error.
Tried searching in this forum, I got a post related to this but links given in the post are not working.
Link
Thanks
Magesh |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
Nic Clouston Advanced
Joined: 01 Feb 2007 Posts: 1075 Topics: 7 Location: At Home
|
Posted: Mon Sep 10, 2018 2:21 pm Post subject: |
|
|
Try entering the text used to define those links - the text in blue - into your search engine and see if you can get the updated links. If you can then add them to your next post. _________________ Utility and Program control cards are NOT, repeat NOT, JCL. |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
kolusu Site Admin
![Site Admin Site Admin](images/ranks/adminpic.jpg)
Joined: 26 Nov 2002 Posts: 12369 Topics: 75 Location: San Jose
|
Posted: Mon Sep 10, 2018 3:17 pm Post subject: |
|
|
Magesh_J,
First things first.
Your SELECT statement in the cursor is looking for SELECT R.ACC_NO but the R table is your temp table is SESSION.TEMP R which has ONLY A_NO as the column. You do realize that A_no <> ACC_NO ?
Apart from that obvious mistake, Did you CONNECT to the remote database before you opened the cursor?
As Nic pointed you can search with text of the links in google and get the knowledge center links
https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/intro/src/tpc/db2z_distributeddataaccess.html
Btw I think it is an expensive to JOIN the temp table as INNER JOIN when you have multiple tables _________________ Kolusu
www.linkedin.com/in/kolusu |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
Magesh_J Intermediate
![](images/avatars/gallery/members/Sqr.jpg)
Joined: 21 Jun 2014 Posts: 259 Topics: 54
|
Posted: Wed Sep 26, 2018 1:17 pm Post subject: |
|
|
Thanks Nic and Kolusu,
kolusu wrote: | Your SELECT statement in the cursor is looking for SELECT R.ACC_NO but the R table is your temp table is SESSION.TEMP R which has ONLY A_NO as the column. You do realize that A_no <> ACC_NO ?
|
Sorry, It's a mistake while trying to mask the variable.
kolusu wrote: |
Apart from that obvious mistake, Did you CONNECT to the remote database before you opened the cursor
|
The code is working and fetching all the accounts from the remote table when I try without the temporary table.
now, I am fetching all the accounts and doing a Sort Join to fetch the required records.
We are using DRDA to connect the remote database.
Regards,
Magesh |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
kolusu Site Admin
![Site Admin Site Admin](images/ranks/adminpic.jpg)
Joined: 26 Nov 2002 Posts: 12369 Topics: 75 Location: San Jose
|
Posted: Wed Sep 26, 2018 4:06 pm Post subject: |
|
|
Magesh_J wrote: |
The code is working and fetching all the accounts from the remote table when I try without the temporary table.
now, I am fetching all the accounts and doing a Sort Join to fetch the required records.
|
Magesh_J,
The problem is that the TEMPORARY table is available is ONLY available during run time. So you probably have to create the temp table in the main program and perform the join on remote table via a dynamic sub program. Look up the BIND parameter REOPT to make DB2 get the updated access path. _________________ Kolusu
www.linkedin.com/in/kolusu |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
Magesh_J Intermediate
![](images/avatars/gallery/members/Sqr.jpg)
Joined: 21 Jun 2014 Posts: 259 Topics: 54
|
Posted: Fri Sep 28, 2018 11:22 am Post subject: |
|
|
Thanks Kolusu,
I will try this.
Regards,
Magesh |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
|
|