MVSFORUMS.com

[Sqlcode]

Could anyone give me solution to below mentioned condition?

I want to use Partition Unload for a table. I have table T1 which has 50 partition today, I am doing partition unload on the table and hence it will create 50 dataset.

I am using all 50 partitioned unload datasets in the next step which is SORT. Sort step needs to take all 50 partition unload as input.

Now, let say after a month DBA decides to add 10 more partition then Unload dataset will get created but my SORT step, in which 50 datasets are hardcoded and hence it won't pick up new 10 unload partition datasets.

Questions:-

1) I thought of Unloading partitions into GDG versions but Db2 is not letting create unload datasets that are GDGs.

2) I am able to create 50 flat files, but then Everytime DBA adds partitions, I need to change my proc to include new partition datasets.

Is there a way to automate this situation?

Thanks,

[kingo]

[Sqlcode]

Hi Kingo,
I am creating GDG using IDCAMS

//STEP01 EXEC PGM=IDCAMS
//*
//SYSPRINT DD SYSOUT=*
//SYSIN DD *
DEFINE GDG -
(NAME(MYID.TABLE.UNLOAD) -
LIMIT(60))
/*

What I meant by 1st point is after creating GDG version, when I try to submit partition unload job, DB2 is giving me error message saying.

Partitioned Unload Job :-

//SYSIN DD *
TEMPLATE UNLDDS DSN MYID.TABLE.&TS..UNLOAD(&PART)
UNIT SYSDA DISP (MOD,CATLG,CATLG)
TEMPLATE PNCHDS DSN MYID.TABLE4.&TS..P&PART..CARDS
UNIT SYSDA DISP (MOD,CATLG,CATLG)

UNLOAD DATA
PUNCHDDN PNCHDS
UNLDDN UNLDDS
FROM TABLE TABLE_NAME
SHRLEVEL CHANGE ISOLATION UR
/*


Error message which I am getting is,

DSNU049I DSNUGPRS - INVALID OPERAND '&PART' FOR KEYWORD 'TEMPLATE'

[kingo]

Please try

[jsharon1248]

You're making the task more difficult than it needs to be. You're not unloading by partitions right now, so just drop the &PART symbolic in the TEMPLATE:

[Sqlcode]

Hi Sharon,
Thanks for suggestion but it seems like if I use TEMPLATE UNLDDS DSN MYID.TABLE.&TS..UNLOAD, then DB2 will simply do table unload, it won't do partition unload.

I may be wrong but I dont see multiple thread in db2 for unload job, rather it shows only single thread.

[jsharon1248]

rajen

Let's walk through this. You want to execute an UNLOAD by partition, and then execute a sort to combine all the individual datasets into one. What would you lose by just doing a full table unload into 1 dataset, and then just sort that 1 dataset? What you would gain is a simple, easy to understand solution to your problem. What do you think you will gain by utilizing the UNLOAD by partition? The UNLOAD performance might be a little better, but my guess is that the difference wouldn't justify a complicated solution. Try the UNLOAD both ways to see what the difference in performance really is.

[Sqlcode]

Sharon,

You caught me right. We have performance issue for ELAPSED TIME.

Currently full table unload (190 Million Records) takes about 1 hour, Since I need to reduce that time to reasonable low period, I have decided to use Partition Unload,

Now, I could have simply hardcoded Partitioned Unload Datasets in to SORT Step and it would have been an easy life but then What if DBA decides to add few more partition and I am not informed.

So, I thought it would be wise to create UNLDDDS in to GDG and then use GDG base as input for SORT so that SORT would automatically pickup all the versions(i.e. Partition Unload Dataset).

Let me know if you have some solution or if it is still not clear.

Thanks,

[jsharon1248]

You didn't address the most important issue. What is the elapsed time difference between the full table UNLOAD, and the UNLOAD by partition? Seems to me an hour isn't all that bad for 190 million rows. Also, did you try allocating additional buffers for the output dataset?

[Sqlcode]

Ok,

Partition Unload takes :- 09 Mins of Elapsed Time
Full Table Unload Takes :- 60-65 Mins of Elapsed Time

The reason for this approach is, we dont really have Elapsed Time Window and hence we decided that We should atleast try for Partition Unload if its feasible but I got stuck into above mentioned problem.

Yes, I did try allocating extra buffers as well as virtual tapes/DASD. 60-65 Mins of Elapsed Time is based on Highest Buffer allowed at our shop with DASD and its almost same for Virtual Tapes as well.

Thanks,

[shantharam_k]

I think you have two round about ways:

Approach1:

1. Create a number of empty datasets (say 200)
2. Then unload the data using TEMPLATE specifying partitions into these datasets. Only some of the datasets would be used in the unload ( as many as the no of partitions).
3. In the SORT step specify all the 200 datasets. Of course, some empty datasets will participate in the SORT
4. When you feel that the no of partitions is likely to go beyond 200, u can create another 200 & so on

Approach1:

1. Run a first JCL to get the NUMPARTS in the tablespace. Using the NUMPARTS information, dynamically create a JCL that will specify the exact number of UNLOAD datasets and SORT datasets.
2. Submit the JCL created in the first step

Thanks
Shan

[Sqlcode]

Shan,

I have tried first approach but I am keeping it a side for the temp. purpose since it is associated with many unused datasets.

Would you mind throwing little more light on second approach, I am sorry but I didn't really understood the approach. How would u achieve that? Do u have sample JCL?

Thanks,

[shantharam_k]

Here it is :

1. First Write a Program in REXX to get the no of Partitions in the TS by querying SYSIBM.SYSTABLESPACE (U can also run IKJEFT01 to get this using DSNTEP2 & scan the output of this to retrieve the NUMPARTS)
2. After getting the NUMPARTS, From the REXX program write the unload JCL into a PDS member. For ex. if you have 4 parts in TS, the REXX program should write the JCL in such a way that the JCL can unload into 4 datasets& sort 4 datasets.
3. Submit the dynamic JCL created (this should be achievable - for example you can do this is using the event trigger mechanism of your job scheduler. or u could also submit the JCL from the rexx program itself)

Hope this works 4 you

[jsharon1248]

So there is a significant performance advantage to unloading by partition. The only other option I can think of is to invoke the SORT from a COBOL program. You would have to dynamically allocate the unload files. Search the forum using the keywork BPXWDYN to find posts for dynamically allocating files in COBOL. You'd need to build the list of files using REXX.

[Sqlcode]

Amm..
Its kind of tricky question.

There is always improvement in terms of ELAPSED TIME since DB2 invokes Multiple Threads for unload from each partitions but this is achieved at the cost of slightly higher CPU Cost cause Multithreading.

I have used Dynamic Allocation earlier but couldnt think of generating Unload files based on this. I missed that.

Thanks for the information.

Thanks,