Can we write a subquery on Imagecopy

satyender · New User Joined: 05 Jan 2008 Posts: 40 Location: hyderabad

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.

vebs · New User Joined: 27 Oct 2007 Posts: 15 Location: UK

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 !!

satyender · New User Joined: 05 Jan 2008 Posts: 40 Location: hyderabad

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.

Suresh Ponnusamy · Posted: Tue Aug 05, 2008 10:51 pm Post subject:

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.

satyender · New User Joined: 05 Jan 2008 Posts: 40 Location: hyderabad

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 )

satyender · New User Joined: 05 Jan 2008 Posts: 40 Location: hyderabad

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

Thanks in advance.

dick scherrer · Posted: Wed Aug 06, 2008 12:37 am Post subject:

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.

satyender · New User Joined: 05 Jan 2008 Posts: 40 Location: hyderabad

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.

Craq Giegerich · Posted: Wed Aug 06, 2008 12:49 am Post subject:

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.

dick scherrer

You're welcome - good luck

d