Db2 V11 in zOS - Calling a CICS module from NSP and passing data in 'Created global temp table'

Anandh Nagarajan

Db2 V11 in zOS - Calling a CICS module from NSP and passing data in 'Created global temp table'

I am trying to call a CICS program from a Native SQL Stored Proc in Db2 V11. I was able to successfully use the SP DSNACICS to call the CICS module but my problem is that i need to pass lot of data to the CICS module via created global temp tables.

I inserted some data to a temp table in NSP , called the CICS module via DSNACICS, but the data in the temp table is not present when i read it in the CICS module. I used the SYNC_OPT as 2 in the NSP since i was unable to make the SYNC_OPT =1 working. I am getting the following error.

'DSNA305I THE CICS EXCI DPL_REQUEST     REQUEST FAILED.  CICS RESPONSE CODE = X'0008' CICS REASON CODE = X'00000204' '

I could format the temp table data into a XML and pass it to the CICS module but my temp data is too huge to fit into the 32704 byte limit that is allowed in DSNACICS.

I tried to find a way to create Containers and Channels in NSP but based on my knowledge and research, i don't think it's doable in NSP.

Any help on this is much appreciated.

 

PS : It has to be temp tables since the CICS application is not ours and it consumes the data thru temp tables. We are trying to modernize one of our heavily used CICS transaction to NSP to be zIIP eligible. 

 

Thanks,

Anandh

 

 

Tony Saul

Db2 V11 in zOS - Calling a CICS module from NSP and passing data in 'Created global temp table&
(in response to Anandh Nagarajan)
I haven't had a need to use DSNACICS, but it looks to be using the CICS EXCI interface that is limited to 32K COMMAREA that can be passed. I think it was CICS TS 5.4 that introduced the use of CONTAINERS with EXCI that allows up to 2GB to be passed, but I don't think DSNACICS has been changed to utilize CONTAINERS instead of COMMAREA.
I don't see any sample code for DSNACICS, so if you are on CICS TS 5.4 or above ,you might have to look at  writing your own Stored Procedure that uses CONTAINERS. 

Regards, Tony

On Thursday, 30 May 2019, 3:38:50 am ACST, Anandh Nagarajan <[login to unmask email]> wrote:


I am trying to call a CICS program from a Native SQL Stored Proc in Db2 V11. I was able to successfully use the SP DSNACICS to call the CICS module but my problem is that i need to pass lot of data to the CICS module via created global temp tables.

I inserted some data to a temp table in NSP , called the CICS module via DSNACICS, but the data in the temp table is not present when i read it in the CICS module. I used the SYNC_OPT as 2 in the NSP since i was unable to make the SYNC_OPT =1 working. I am getting the following error.

'DSNA305I THE CICS EXCI DPL_REQUEST     REQUEST FAILED.  CICS RESPONSE CODE = X'0008' CICS REASON CODE = X'00000204' '

I could format the temp table data into a XML and pass it to the CICS module but my temp data is too huge to fit into the 32704 byte limit that is allowed in DSNACICS.

I tried to find a way to create Containers and Channels in NSP but based on my knowledge and research, i don't think it's doable in NSP.

Any help on this is much appreciated.

 

PS : It has to be temp tables since the CICS application is not ours and it consumes the data thru temp tables. We are trying to modernize one of our heavily used CICS transaction to NSP to be zIIP eligible. 

 

Thanks,

Anandh

 

 

Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
ESAi has well-regarded tools for Fast Cloning, Buffer Pool Tuning, Log Analysis, TDM & more.
BCV4, BCV5, BPA4DB2, ULT4DB2... modern power tools to get the job done faster & easier than ever.
http://www.ESAIGroup.com/idug



Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2

Tony Saul

Db2 V11 in zOS - Calling a CICS module from NSP and passing data in 'Created global temp table&
(in response to Anandh Nagarajan)
I haven't had a need to use DSNACICS, but it looks to be using the CICS EXCI interface that is limited to 32K COMMAREA that can be passed. I think it was CICS TS 5.4 that introduced the use of CONTAINERS with EXCI that allows up to 2GB to be passed, but I don't think DSNACICS has been changed to utilize CONTAINERS instead of COMMAREA.
I don't see any sample code for DSNACICS, so if you are on CICS TS 5.4 or above ,you might have to look at  writing your own Stored Procedure that uses CONTAINERS. 

Regards, Tony

On Thursday, 30 May 2019, 3:38:50 am ACST, Anandh Nagarajan <[login to unmask email]> wrote:


I am trying to call a CICS program from a Native SQL Stored Proc in Db2 V11. I was able to successfully use the SP DSNACICS to call the CICS module but my problem is that i need to pass lot of data to the CICS module via created global temp tables.

I inserted some data to a temp table in NSP , called the CICS module via DSNACICS, but the data in the temp table is not present when i read it in the CICS module. I used the SYNC_OPT as 2 in the NSP since i was unable to make the SYNC_OPT =1 working. I am getting the following error.

'DSNA305I THE CICS EXCI DPL_REQUEST     REQUEST FAILED.  CICS RESPONSE CODE = X'0008' CICS REASON CODE = X'00000204' '

I could format the temp table data into a XML and pass it to the CICS module but my temp data is too huge to fit into the 32704 byte limit that is allowed in DSNACICS.

I tried to find a way to create Containers and Channels in NSP but based on my knowledge and research, i don't think it's doable in NSP.

Any help on this is much appreciated.

 

PS : It has to be temp tables since the CICS application is not ours and it consumes the data thru temp tables. We are trying to modernize one of our heavily used CICS transaction to NSP to be zIIP eligible. 

 

Thanks,

Anandh

 

 

Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
ESAi has well-regarded tools for Fast Cloning, Buffer Pool Tuning, Log Analysis, TDM & more.
BCV4, BCV5, BPA4DB2, ULT4DB2... modern power tools to get the job done faster & easier than ever.
http://www.ESAIGroup.com/idug



Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2

James Campbell

Db2 V11 in zOS - Calling a CICS module from NSP and passing data in 'Created global temp table&
(in response to Anandh Nagarajan)
I know this is a kludge, but ...

- create a permanent table with appropriate keys. Because of (well!) don't use a LOB.
- in the NSP, get some unique value (e.g. GENERATE_UNIQUE)
- insert data into the permanent table with the unique value as its key
- COMMIT
- call dsnacics, passing the unique value
- the cics program can now pick data off the table, using the unique value. It can insert the
data into your created GTT for whatever comes after it
- at the end of everything delete the, now no longer required, rows.

Every so often, ensure that there is no old data let in the permanent table.

James Campbell


On 29 May 2019 at 11:08, Anandh Nagarajan wrote:

>
> I am trying to call a CICS program from a Native SQL Stored Proc in Db2 V11. I was able to
> successfully use the SP DSNACICS to call the CICS module but my problem is that i need to pass
> lot of data to the CICS module via created global temp tables.
> I inserted some data to a temp table in NSP , called the CICS module via DSNACICS, but the
> data in the temp table is not present when i read it in the CICS module. I used the SYNC_OPT as
> 2 in the NSP since i was unable to make the SYNC_OPT =1 working. I am getting the following
> error.
> 'DSNA305I THE CICS EXCI DPL_REQUEST     REQUEST FAILED.  CICS RESPONSE CODE =
> X'0008' CICS REASON CODE = X'00000204' '
> I could format the temp table data into a XML and pass it to the CICS module but my temp data is
> too huge to fit into the 32704 byte limit that is allowed in DSNACICS.
> I tried to find a way to create Containers and Channels in NSP but based on my knowledge and
> research, i don't think it's doable in NSP.
> Any help on this is much appreciated.
>  
> PS : It has to be temp tables since the CICS application is not ours and it consumes the data thru
> temp tables. We are trying to modernize one of our heavily used CICS transaction to NSP to be
> zIIP eligible. 
>  
> Thanks,
> Anandh
>  
>  


---
This email has been checked for viruses by AVG.
https://www.avg.com

Michael Hannan

RE: Db2 V11 in zOS - Calling a CICS module from NSP and passing data in 'Created global temp table&
(in response to James Campbell)

In Reply to James Campbell:

I know this is a kludge, but ...

- create a permanent table with appropriate keys. Because of (well!) don't use a LOB.
- in the NSP, get some unique value (e.g. GENERATE_UNIQUE)
- insert data into the permanent table with the unique value as its key
- COMMIT
- call dsnacics, passing the unique value
- the cics program can now pick data off the table, using the unique value. It can insert the
data into your created GTT for whatever comes after it
- at the end of everything delete the, now no longer required, rows.

Every so often, ensure that there is no old data let in the permanent table.

James Campbell

I have never been a big fan of Created Temps. Can we assume the called CICS process has to run on Same DB2 subsystem member as the Native Stored Proc (I don't fully understand the architecture)?

Quoting IBM:

"If the CICS server program that DSNACICS invokes accesses Db2 resources, the server program runs under a separate unit of work from the original unit of work that calls the stored procedure. This means that the CICS server program might deadlock with locks that the client program acquires."

This seems to explain why the Global Temp does not work.

If so, then maybe the Native STPROC need not Commit perhaps (in case other changes not ready to commit), and the CICS process could use WITH UR on the read (not waiting for a commit if it is in a different thread/UOW). Return to the Stored Proc (is this a Synchronous thing? I have assumed it), then do cleanup Delete before COMMIT. I am thinking this has then no chance of leaving junk lying around. Use Row Level Locking on the table. 

If the CICS process runs against a different DB2 Data Sharing member, then there is a chance its read of the data could miss the Insert completion. Global shared Lock is needed to guarantee to see the latest data. Even Read WITH CS (uses lock avoidance) is not good enough to guarantee to see the latest copy of the page. The alternative is to use COMMIT as James suggested and have CICS process wait to get a lock. That might have to be Lock for Update or maybe Repeatable Read, not quite sure.  

If there is no Data Sharing or no chance of being on a different Data Sharing member, then these complications go away. 

I may have made this too confusing now. 

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Anandh Nagarajan

RE: Db2 V11 in zOS - Calling a CICS module from NSP and passing data in 'Created global temp table&
(in response to Michael Hannan)

Thanks Tony,James and Mike.

1. Instead of temp tables, use a main table with a unique key to identify the data that is for a particular transaction. This is a very heavily used transaction and i don't think it will be a good idea to use a normal table as a scratchpad. That was one of the reasons why temp tables were used.

2. I tried creating my own Cobol SP which was called from NSP. The data that NSP write in the temp table is visible in the COBOL SP but the reverse is not true. 

 

At this point i am trying the following.

1. Format the temp table data as XML in NSP and call the Cobol SP with NSP as a parm.

2. Parse the XML in the Cobol SP and insert into the temp tables and then call the CICS trans.

3. Format the results of the CICS trans as XML and pass it back to NSP.

 

I will update if am successful in this experiment. 

Daniel Luksetich

Db2 V11 in zOS - Calling a CICS module from NSP and passing data in 'Created global temp table&
(in response to Anandh Nagarajan)
I believe the problem is that the temp table is only visible under the current thread. When you can the CICS stored procedure that is under a different task, and thus a different thread. The shared permanent table is the solution.

Cheers,

Dan



Daniel L Luksetich

DanL Database Consulting



IBM GOLD Consultant

IBM Champion for Analytics

IDUG Content Committee Past-Chairman

IDUG DB2-L Administrator

IBM Certified Database Adminstrator – DB2 11 DBA for z/OS

IBM Certified System Administrator – DB2 11 for z/OS

IBM Certified Application Developer – DB2 11 for z/OS

IBM Certified Advanced Database Administrator – DB2 10.1 for Linux UNIX and Windows



From: Anandh Nagarajan <[login to unmask email]>
Sent: Friday, May 31, 2019 1:43 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Db2 V11 in zOS - Calling a CICS module from NSP and passing data in 'Created global temp table&



Thanks Tony,James and Mike.

1. Instead of temp tables, use a main table with a unique key to identify the data that is for a particular transaction. This is a very heavily used transaction and i don't think it will be a good idea to use a normal table as a scratchpad. That was one of the reasons why temp tables were used.

2. I tried creating my own Cobol SP which was called from NSP. The data that NSP write in the temp table is visible in the COBOL SP but the reverse is not true.



At this point i am trying the following.

1. Format the temp table data as XML in NSP and call the Cobol SP with NSP as a parm.

2. Parse the XML in the Cobol SP and insert into the temp tables and then call the CICS trans.

3. Format the results of the CICS trans as XML and pass it back to NSP.



I will update if am successful in this experiment.



-----End Original Message-----

Daniel Luksetich

Db2 V11 in zOS - Calling a CICS module from NSP and passing data in 'Created global temp table&
(in response to Daniel Luksetich)
Sorry, I mean call the CICS stored procedure. I think the permanent table will perform just fine, but you may need frequent REORGs. Keep tabs on it.



Daniel L Luksetich

DanL Database Consulting



IBM GOLD Consultant

IBM Champion for Analytics

IDUG Content Committee Past-Chairman

IDUG DB2-L Administrator

IBM Certified Database Adminstrator – DB2 11 DBA for z/OS

IBM Certified System Administrator – DB2 11 for z/OS

IBM Certified Application Developer – DB2 11 for z/OS

IBM Certified Advanced Database Administrator – DB2 10.1 for Linux UNIX and Windows



From: Daniel L Luksetich <[login to unmask email]>
Sent: Friday, May 31, 2019 2:25 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Db2 V11 in zOS - Calling a CICS module from NSP and passing data in 'Created global temp table&



I believe the problem is that the temp table is only visible under the current thread. When you can the CICS stored procedure that is under a different task, and thus a different thread. The shared permanent table is the solution.

Cheers,

Dan



Daniel L Luksetich

DanL Database Consulting



IBM GOLD Consultant

IBM Champion for Analytics

IDUG Content Committee Past-Chairman

IDUG DB2-L Administrator

IBM Certified Database Adminstrator – DB2 11 DBA for z/OS

IBM Certified System Administrator – DB2 11 for z/OS

IBM Certified Application Developer – DB2 11 for z/OS

IBM Certified Advanced Database Administrator – DB2 10.1 for Linux UNIX and Windows



From: Anandh Nagarajan <[login to unmask email] <mailto:[login to unmask email]> >
Sent: Friday, May 31, 2019 1:43 PM
To: [login to unmask email] <mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Db2 V11 in zOS - Calling a CICS module from NSP and passing data in 'Created global temp table&



Thanks Tony,James and Mike.

1. Instead of temp tables, use a main table with a unique key to identify the data that is for a particular transaction. This is a very heavily used transaction and i don't think it will be a good idea to use a normal table as a scratchpad. That was one of the reasons why temp tables were used.

2. I tried creating my own Cobol SP which was called from NSP. The data that NSP write in the temp table is visible in the COBOL SP but the reverse is not true.



At this point i am trying the following.

1. Format the temp table data as XML in NSP and call the Cobol SP with NSP as a parm.

2. Parse the XML in the Cobol SP and insert into the temp tables and then call the CICS trans.

3. Format the results of the CICS trans as XML and pass it back to NSP.



I will update if am successful in this experiment.



-----End Original Message-----



-----End Original Message-----

Daniel Luksetich

Db2 V11 in zOS - Calling a CICS module from NSPand passing data in 'Created global temp table&a
(in response to Daniel Luksetich)
Sorry, need to clarify further. DSNACICS is under the thread of the caller, but once it goes to CICS I believe it is separate. DanSent from my T-Mobile 4G LTE Device
-------- Original message --------From: Daniel L Luksetich <[login to unmask email]> Date: 5/31/19 3:25 PM (GMT-05:00) To: [login to unmask email] Subject: [DB2-L] - RE: Db2 V11 in zOS - Calling a CICS module from NSP and passing data in 'Created global temp table& I believe the problem is that the temp table is only visible under the current thread. When you can the CICS stored procedure that is under a different task, and thus a different thread. The shared permanent table is the solution.Cheers,Dan Daniel L LuksetichDanL Database Consulting IBM GOLD ConsultantIBM Champion for AnalyticsIDUG Content Committee Past-ChairmanIDUG DB2-L AdministratorIBM Certified Database Adminstrator – DB2 11 DBA for z/OSIBM Certified System Administrator – DB2 11 for z/OSIBM Certified Application Developer – DB2 11 for z/OSIBM Certified Advanced Database Administrator – DB2 10.1 for Linux UNIX and Windows From: Anandh Nagarajan <[login to unmask email]> Sent: Friday, May 31, 2019 1:43 PMTo: [login to unmask email]: [DB2-L] - RE: Db2 V11 in zOS - Calling a CICS module from NSP and passing data in 'Created global temp table& Thanks Tony,James and Mike.1. Instead of temp tables, use a main table with a unique key to identify the data that is for a particular transaction. This is a very heavily used transaction and i don't think it will be a good idea to use a normal table as a scratchpad. That was one of the reasons why temp tables were used.2. I tried creating my own Cobol SP which was called from NSP. The data that NSP write in the temp table is visible in the COBOL SP but the reverse is not true.  At this point i am trying the following.1. Format the temp table data as XML in NSP and call the Cobol SP with NSP as a parm.2. Parse the XML in the Cobol SP and insert into the temp tables and then call the CICS trans.3. Format the results of the CICS trans as XML and pass it back to NSP. I will update if am successful in this experiment.  -----End Original Message-----Site Links:
View post online  
View mailing list online  
Start new thread via email  
Unsubscribe from this mailing list  
Manage your subscription  
This email has been sent to: [login to unmask email]

Visit ESAi at IDUG Charlotte. Enter drawings & learn about BCV4, BCV5, Masking Tool, BPA4DB2...
great automated solutions for Cloning, Buffer Pool Tuning, Log processing, TDM & more.
http://www.ESAIGroup.com/idug

Use of this email content is governed by the terms of service at:http://www.idug.org/p/cm/ld/fid=2