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
 

 

Can we write a subquery on Imagecopy

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

New User


Joined: 05 Jan 2008
Posts: 50
Location: Olympia, WA, USA

PostPosted: Tue Aug 05, 2008 9:51 pm    Post subject: Can we write a subquery on Imagecopy
Reply with quote

Hi all,

The following is the scenario.
I have to unload the data from the production. But as I am not supposed to hit a query in production, is it is possible to write a sub-query or join on Imagecopies.

for example:
SELECT
*
FROM
TABLE1
WHERE RDNDTE IN
(SELECT
RDNDTE
FROM
TABLE2
WHERE DATE(RDNDTE) >= CURRENT DATE - 5 YEARS);


Please let me know, if there are any chances of executing a query on this kind of scenario.

Thanks in advance to all.
Back to top
View user's profile Send private message

vebs

New User


Joined: 27 Oct 2007
Posts: 19
Location: UK

PostPosted: Tue Aug 05, 2008 10:24 pm    Post subject:
Reply with quote

Sorry, I wasnt able to understand your SQL..

But the answere is no.. if you are not allowed to query a table, you cannot access Imagecopies as well, they will be under restricted access, because ultimately, the DB2 tables also end up in some dataset, isnt it !!
Back to top
View user's profile Send private message
satyender

New User


Joined: 05 Jan 2008
Posts: 50
Location: Olympia, WA, USA

PostPosted: Tue Aug 05, 2008 10:28 pm    Post subject:
Reply with quote

Vebs,

Thanks for your response.
My question is can we unload the data from imagecopies using joins or sub-queires.

I can query on tables. But I am asked not to hit a query in Production as this query takes lot of time to unload the data from production.
Back to top
View user's profile Send private message
Suresh Ponnusamy

Active User


Joined: 22 Feb 2008
Posts: 107
Location: New York

PostPosted: Tue Aug 05, 2008 10:51 pm    Post subject:
Reply with quote

Hi satyender

Image copy of data are copying the entire Tablespace to another tablespace.

Again it is the way your Tables are placed in the Tablespace. I have seen in some project they have each table reside in single tablespace. In some, one tablespace has multiple tables in it.

In my project, we are having one table per tablespace and when we request our DBAs for image copy, we can give the specific condition using WHERE Clause.

if your Table1 and Table2 are in the same tablespace, you can use WHERE clause in the image copy. But I am not sure about Join. If it is in different, i think it is not.

Correct me if I am wrong.
Back to top
View user's profile Send private message
satyender

New User


Joined: 05 Jan 2008
Posts: 50
Location: Olympia, WA, USA

PostPosted: Tue Aug 05, 2008 11:16 pm    Post subject:
Reply with quote

Hi Suresh Ponnusamy,
Thanks for your response.

We have one table in one tablespace.And I use generally the following JCL to unload from the imagecopy.But never used joins and queries on imagecopies.I too think that it wont work,but still, I want to know if I am wrong. As I am not that perfect I am looking forward to know more about this.


//UNLOAD EXEC PGM=DSNUTILB,PARM='PDB2,T0J0AJ7'
//IMAGECPY DD DSN=LCSPN.DB2COPY.TLCS003.G1756V00,DISP=SHR
//SYSREC DD DSN=T0J0AJ7.TLCS007.UNLOAD,DISP=(,CATLG,DELETE),
// UNIT=VTAPE
//* UNIT=DISK,SPACE=(CYL,(X,,X),RLSE)
//SYSPUNCH DD DSN=T0J0AJ7.TLCS007.SYSPUNCH,DISP=(,CATLG,DELETE),
// UNIT=DISK,SPACE=(TRK,2)
//SYSPRINT DD SYSOUT=*
//SYSIN DD *
UNLOAD TABLESPACE DBLCSP1.TLCS003 FROMCOPYDDN IMAGECPY
FROM TABLE LEXUS.TLCS003
WHEN (RDNDTE >= CURRENT DATE - 5 YEARS )
Back to top
View user's profile Send private message
satyender

New User


Joined: 05 Jan 2008
Posts: 50
Location: Olympia, WA, USA

PostPosted: Wed Aug 06, 2008 12:26 am    Post subject:
Reply with quote

Please help in this regard. If anyone could provide JCL that would be of great help.

Thanks in advance.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Aug 06, 2008 12:37 am    Post subject:
Reply with quote

Hello,

Please do not wait for jcl. . . If i understand what you want to do, it is not an option.

The image copy dataset is a qsam file and queries may not be run against a qsam file.

If i've misunderstood, please clarify and we'll continue to look for suggestions.
Back to top
View user's profile Send private message
satyender

New User


Joined: 05 Jan 2008
Posts: 50
Location: Olympia, WA, USA

PostPosted: Wed Aug 06, 2008 12:45 am    Post subject:
Reply with quote

Thanks a lot dick scherrer.
I will try in different way and will update in the forum,if i could found answer. Thanks all once again.
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Wed Aug 06, 2008 12:49 am    Post subject:
Reply with quote

You could use sort to extract the RDNDTE from the unload for table2 and use the output of that sort along with unload for table1 to extract (again with sort) the data you need.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Aug 06, 2008 12:51 am    Post subject: Reply to: Can we write a subquery on Imagecopy
Reply with quote

You're welcome - good luck icon_smile.gif

d
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 Error IEC161I 052(009,XXXRS00)-084 wh... amitc23 JCL & VSAM 10 Wed Oct 12, 2016 5:00 pm
No new posts how to DYNAMICALLY write sort cond to... mohamedmubee COBOL Programming 13 Fri Aug 26, 2016 11:46 pm
No new posts Compare 2 files & write Indicator... nagendrapgupta SYNCSORT 5 Sun May 15, 2016 10:42 am
No new posts DFSORT to match two files and write John F Dutcher DFSORT/ICETOOL 6 Thu May 12, 2016 5:17 pm
No new posts IBMHPU - Tablespace and Imagecopy chaitanya bharath DB2 1 Fri Apr 15, 2016 5:54 pm


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