| View previous topic :: View next topic |
| Author |
Message |
Magesh_J
Intermediate
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 |
|
 |
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 |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12365
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 - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
Magesh_J
Intermediate
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 |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12365
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 - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
Magesh_J
Intermediate
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 |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
