|
View previous topic :: View next topic
|
| Author |
Message |
gylbharat
Active Member
.jpg)
Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
|
|
|
|
Hi,
I got a doubt, while I was reading about packages.
Consider a scenario:
Subsystem - DB2A
I have a Package - PackageA.
This package is a cobol program which insert current timestamp in CREATOR1.TABLE1. This Package is in collection collA bound under planA.
The package is bound with QUALIFIER(CREATOR1) so when we run the JCL using appropriate Loadlib and Dbrmlib, PackageA gets executed successfully.
Now suppose, I create a duplicate version of the table as CREATOR2.TABLE1 and bind the same package PackageA using same DBRMLIB with QUALIFIER(CREATOR2) in a different collection say CollB.
also this CollB is part of same planA.
Now when I run the same JCL, how does DB2 identify it has to refer PackageA of CollA or CollB?
I think my question may sound stupid, but it just stuck to my mind. I don't have required access to system test this scenario.
Thanks in advance |
|
| Back to top |
|
 |
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
| Back to top |
|
|
gylbharat
Active Member
Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
|
|
|
|
Thanks Arun... This is very helpful. So I understand that whenever we want to refer to a particular collection, we will pass the correct collection name using linkage section to the program... and call "SET PACKAGESET" statement to point to correct collection.
Also in the thread, there is a question
| Quote: |
Hi UmeySan,
Thanks a lot for such detailed information...
Select territory from sales
Here sales tables can be present in region1,region2 or region3
We can bind it accordingly to the 3 regions and we can set the packageset to any one of the 3 regions as and when required.
But what if I have to access sales in both region1 and region 2 at the same time...? We can make it point either to region1 or region2 only.
Can we access multiple regions...?
Is it possible....?
|
Now suppose, program insert data in territory table. I want to insert data in region1.territory and region2.territory using the same program (without running it two times) then I should 1st use "SET PACKAGESET = 'REGION1' " and execute my insert statement and then "SET PACKAGESET = 'REGION2' " and again execute the insert statement within the same program. Will this work? |
|
| Back to top |
|
|
agkshirsagar
Active Member
Joined: 27 Feb 2007
Posts: 691
Location: Earth
|
|
|
|
This is an interesting question. To best of my knowledge, package release can occur when program is bound with RELEASE(COMMIT) and program issues a COMMIT. If the program is bound with RELEASE(DEALLOCATE), program thread has to terminate before DB2 looks for package again.
I will recommend testing this scenario to confirm the answer. |
|
| Back to top |
|
|
agkshirsagar
Active Member
Joined: 27 Feb 2007
Posts: 691
Location: Earth
|
|
|
|
| Quote: |
| I don't have required access to system test this scenario. |
I just read your original post again. I will try to test this (at my leisure of course) and let you know unless someone else on the forum beats me to it. |
|
| Back to top |
|
|
daveporcelan
Active Member
Joined: 01 Dec 2006
Posts: 792
Location: Pennsylvania
|
|
|
|
| Quote: |
Now when I run the same JCL, how does DB2 identify it has to refer PackageA of CollA or CollB?
|
Which ever program is listed first in your SYSIBM.SYSPACKLIST will be the package used. It is the first one listed when you did a BIND PLAN.
| Quote: |
Now suppose, program insert data in territory table. I want to insert data in region1.territory and region2.territory using the same program (without running it two times) then I should 1st use "SET PACKAGESET = 'REGION1' " and execute my insert statement and then "SET PACKAGESET = 'REGION2' " and again execute the insert statement within the same program. Will this work?
|
This should work. If your 'region1' package is first in the packlist then you do not need the Set Current PACKAGESET command the first time. It will default to that.
| Quote: |
I don't have required access to system test this scenario.
|
I hope this is all theory, becuase this fact makes the whole thing a little scary. |
|
| Back to top |
|
|
agkshirsagar
Active Member
Joined: 27 Feb 2007
Posts: 691
Location: Earth
|
|
|
|
| Quote: |
| If your 'region1' package is first in the packlist then you do not need the Set Current PACKAGESET command the first time. It will default to that. |
Not necessarily. This will be true for the very first execution only. For the next execution, it will depend on RELEASE parameter. If the CURRNET PACKAGESET value is blank, it will use whichever SKPT is available in EDM pool. Please refer to UmeySan's post mentioned in the second response .
In my previous posts I have mentioned the same concept.
My hesitation was , does DB2 look at PACKAGESET value at every SQL statement? or does it look for package after releasing current SKPT only? (which happens after COMMIT or THREAD termination based on RELEASE parameter) .
Apparently, it has to happen after every SQL statement otherwise first switch also wouldn't work. Any objections? |
|
| Back to top |
|
|
daveporcelan
Active Member
Joined: 01 Dec 2006
Posts: 792
Location: Pennsylvania
|
|
|
|
All I can tell you the way we have implemented the process successfully.
Packlist for PLANA:
REGION1.*
REGION2.PROGB
PROGA is Driver Program
CALL PROGB
PROGB executes updating tables in REGION1
GOBACK
PROGA continues
SET CURRENT PACKAGESET = REGION2
CALL PROGB
PROGB executes updating tables in REGION2
GOBACK
PROGA continues
GOBACK (Job done) |
|
| Back to top |
|
|
agkshirsagar
Active Member
Joined: 27 Feb 2007
Posts: 691
Location: Earth
|
|
|
|
What is the RELESE parameter used in package bind?
Above approach will work if RELEASE(COMMIT) was used. RELEASE(DEALLOCATE) may cuase updates in REGION2 for second execution if the last loaded package was of REGION2. |
|
| Back to top |
|
|
daveporcelan
Active Member
Joined: 01 Dec 2006
Posts: 792
Location: Pennsylvania
|
|
|
|
We use: RELEASE(DEALLOCATE)
We may not have an issue because the second execution is a re-invocation of the Plan itself. I guess everthing starts again. |
|
| Back to top |
|
|
gylbharat
Active Member
Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
|
|
|
|
| Thanks Everyone, I have now got more insight into the concept. |
|
| Back to top |
|
|
RV14
New User
Joined: 21 Jun 2020
Posts: 10
Location: United States
|
|
|
|
| daveporcelan wrote: |
All I can tell you the way we have implemented the process successfully.
Packlist for PLANA:
REGION1.*
REGION2.PROGB
PROGA is Driver Program
CALL PROGB
PROGB executes updating tables in REGION1
GOBACK
PROGA continues
SET CURRENT PACKAGESET = REGION2
CALL PROGB
PROGB executes updating tables in REGION2
GOBACK
PROGA continues
GOBACK (Job done) |
Bringing up an old post to clarify a doubt: Can the SET CURRENT PACKAGESET statement be in the (procedure division) of PROGB for this to work? Or does it have to be set BEFORE the DB2 program with the SQLs of interest (PROGB in this case) is invoked so the right package can be picked up? Thanks |
|
| Back to top |
|
|
daveporcelan
Active Member
Joined: 01 Dec 2006
Posts: 792
Location: Pennsylvania
|
|
|
|
This would be the correct choice:
| Quote: |
| Or does it have to be set BEFORE the DB2 program with the SQLs of interest (PROGB in this case) is invoked so the right package can be picked up? |
|
|
| Back to top |
|
|
RV14
New User
Joined: 21 Jun 2020
Posts: 10
Location: United States
|
|
|
|
| daveporcelan wrote: |
This would be the correct choice:
| Quote: |
| Or does it have to be set BEFORE the DB2 program with the SQLs of interest (PROGB in this case) is invoked so the right package can be picked up? |
|
Thank you, that would seem the right and ideal choice. Sorry to press the point, but what will happen if we don't have PROGA and have the SET as the first SQL in PROGB..are we still garanteed the right package will be picked up. Askjing as we would like NOT to have another program A just for this purpose if we can avoid it. Also, is there a way for us to check what collections DB2 checked in before it picked a package during a program execution. This way, we can actually test it out. |
|
| Back to top |
|
|
daveporcelan
Active Member
Joined: 01 Dec 2006
Posts: 792
Location: Pennsylvania
|
|
|
|
You need to control which version (package) of PROGB is being picked up. I do not know what order the Packlist for the plan is searched. I would guess top down. PROGA is the 'gatekeeper' so to speak. It controls which region is being processed. Without it you don't really know for sure.
Please note that we use this design only when multiple regions need to accessed during the same execution of the Job.
Normally a Plan only accesses a single region. Much better and safer. |
|
| Back to top |
|
|
|
|
