Re: Stress Test DB2 from CICS Socket Interface

Venkat (PCA) Pillay

Re: Stress Test DB2 from CICS Socket Interface
(in response to Mona Syed)
I am on the other side of fence. I haven't done CICS programming for years
and almost forgotten.

Below is an example of Assembler trying run dynamic SQL.

L R6,=A(WORKEND-WORKAREA) GET LENGTH STORAGE REQUIRED
GETMAIN RC,LV=(R6) GET THE STORAGE
ST R15,RETHIGH SAVE RETURN CODE
LTR R15,R15 CHECK RETURN CODE
BNZ RETURN END IF NOT OK ELSE ....
LR R10,R1
....
....
EXEC SQL DECLARE <cursor name> CURSOR FOR <statement name>

EXEC SQL DECLARE <statement name> STATEMENT
LA R8,SQLDASPC <------ SQLDA AREA

USING SQLDA,R8 <----- SQLDA DSECT

LA R6,SQLVAR
USING SQLVARN,R6
MVC SQLDAID,=C'SQLDA '
MVC SQLDABC,=F'60'
MVC SQLN,=H'1'
LA R4,CAAREA <---- SQLCA AREA
USING CA,R4
EXEC SQL PREPARE CNT INTO :SQLDASPC FROM :< var containing SQL
statement>
* check your sql code
EXEC SQL OPEN <cursor name>
LA R2,<sql output var>
ST R2,SQLDATA
* check your sql code
EXEC SQL FETCH CT USING DESCRIPTOR :SQLDASPC
* perform other logic
EXEC SQL CLOSE <cursor name>


HTH
Venkat Pillay
> -----Original Message-----
> From: Mona Syed [SMTP:[login to unmask email]
> Sent: Tuesday, December 21, 1999 11:48 AM
> To: [login to unmask email]
> Subject: Stress Test DB2 from CICS Socket Interface
>
> Dear DB2 Listers,
>
> I am a CICS Systems Programmer and have never worked with a DB2/SQL
> program. I have the need to write a driver program (in Assembler) that
> will stress test DB2 access from CICS TCP/IP socket interface.
>
> I will need to use Dynamic SQL because the input to construct my SELECT
> statement will come from a temporary storage queue that my program will
> read. The output from the SQL SELECT statement will be a fixed-list
> result table. The row content (columns) will be the same always, but
> the number of rows returned will vary. Here's what my program needs
> to do:
>
> 1. My program will be started my another driver program that will send a
> COMMAREA with the name of a temporary storage queue.
>
> 2. Read the temp storage queue that will specify the WHERE clause values
> for the SELECT statement
>
> 3. Include an SQLCA
>
> 4. Load the input SQL statement into a data area
>
> 5. Declare a Cursor
>
> 6. Prepare the Select Statement
>
> 7. Open Cursor
>
> 8. Alloate space (Getmain ??) for the result table
>
> 9. Fetch Rows from the result table (I need to fetch all the rows by
> going through a loop until there are no-more-rows-to-fetch). I need to
> write all the rows fetched (in one chunk) to another temp storage queue
> and then return to CICS
>
> 10. Close Cursor
>
> 11. Handle any error/return codes from DB2
>
> 12. My program will be started 100s of times simultaneously for the
> purpose of stress testing DB2 access.
>
> From the above, I may give out the impression that I already know what
> to do. I basically pulled these out from the Application Programming
> and SQL Guide. I need a practical example of how to do the above.
>
> If anybody can provide me with some sample code that does more or less
> what I need to do (in Assembler language), it will help me a lot.
>
>
> --
>
> Thanks!
> Mona Syed
> JCPenney Systems Architecture/CICS
>
>
>
>
>



Viswanathan N

Re: Stress Test DB2 from CICS Socket Interface
(in response to Venkat (PCA) Pillay)
hi Mona !!

First of all why are u trying to do the dynamic SQL query program thru
the Assembler . YOu have better and simple options in the form of cobol . I
have the cobol code which will be firing dynamic sql's after a getmain .
This option can fire the queries from the cics terminals.

regards
Vishy



Michael Backes

Re: Stress Test DB2 from CICS Socket Interface
(in response to Viswanathan N)
sorry for wasting your time if you know all this already. but what you are
trying to do sounds way beyond the point of where sockets even cares about
what you are doing. i am assuming the driver program calling yours is the
pgm associated with the transaction being started by sockets. and once out
of sockets the
real test is your db2 performance, cics/db2 interface, tcpip network speed.

sockets doesnt talk to db2, it is cics which talks to db2. sockets is just a
way to establish a connection via tcpip to a cics program.
first thing any inbound stream to sockets does exit to the ezacicse security
module, if i passes there, it will create a socket, then issue cics start
command to start whatever transaction the inbound stream said to start. the
first thing the pgm assicated with that transaction does is a takesocket.
at that point your cics pgm is receiving the data straight from whatever.
usually a .dll file on a pc/server?? some kind of gui thing. the socket
stays alive for as long as that one transaction is running. when its done
doing what its going to do the socket frees up.
so you see sockets has very little to do with actually getting to db2.

all that being said there are some parameters on the cskl transaction that
if they arent set right can cause big headaches. the maximum number of
sockets, fastrd(this could be a make or break one, at least it was for us),
ntasks, socket timeout...

just look at like sockets is the cics green screen you dont have for a pc
application.
if your sitting at a cics screen you need to type a transaction to get
things started.
same with a pc using tcpip to cics. you need something to start a cics
transaction for you and open a line of communication and that is what/all
sockets does.

if your looking for some kind of numbers we are running approx 8 - 15
transaction per second
through sockets.(8 - 15 transactions per second are started i should say) in
a production environment.

again if you knew all this already, sorry for wasting your time.
sorry i couldnt help more on your actual question regarging a pgm with
dynamic sql., hopefully this background on sockets will make things a little
clearer.



Mike Backes
e-mail: [login to unmask email]


-----Original Message-----
From: Mona Syed [mailto:[login to unmask email]
Sent: Tuesday, December 21, 1999 10:48 AM
To: [login to unmask email]
Subject: Stress Test DB2 from CICS Socket Interface


Dear DB2 Listers,

I am a CICS Systems Programmer and have never worked with a DB2/SQL
program. I have the need to write a driver program (in Assembler) that
will stress test DB2 access from CICS TCP/IP socket interface.

I will need to use Dynamic SQL because the input to construct my SELECT
statement will come from a temporary storage queue that my program will
read. The output from the SQL SELECT statement will be a fixed-list
result table. The row content (columns) will be the same always, but
the number of rows returned will vary. Here's what my program needs
to do:

1. My program will be started my another driver program that will send a
COMMAREA with the name of a temporary storage queue.

2. Read the temp storage queue that will specify the WHERE clause values
for the SELECT statement

3. Include an SQLCA

4. Load the input SQL statement into a data area

5. Declare a Cursor

6. Prepare the Select Statement

7. Open Cursor

8. Alloate space (Getmain ??) for the result table

9. Fetch Rows from the result table (I need to fetch all the rows by
going through a loop until there are no-more-rows-to-fetch). I need to
write all the rows fetched (in one chunk) to another temp storage queue
and then return to CICS

10. Close Cursor

11. Handle any error/return codes from DB2

12. My program will be started 100s of times simultaneously for the
purpose of stress testing DB2 access.

From the above, I may give out the impression that I already know what
to do. I basically pulled these out from the Application Programming
and SQL Guide. I need a practical example of how to do the above.

If anybody can provide me with some sample code that does more or less
what I need to do (in Assembler language), it will help me a lot.


--

Thanks!
Mona Syed
JCPenney Systems Architecture/CICS








Mona Syed

Stress Test DB2 from CICS Socket Interface
Dear DB2 Listers,

I am a CICS Systems Programmer and have never worked with a DB2/SQL
program. I have the need to write a driver program (in Assembler) that
will stress test DB2 access from CICS TCP/IP socket interface.

I will need to use Dynamic SQL because the input to construct my SELECT
statement will come from a temporary storage queue that my program will
read. The output from the SQL SELECT statement will be a fixed-list
result table. The row content (columns) will be the same always, but
the number of rows returned will vary. Here’s what my program needs
to do:

1. My program will be started my another driver program that will send a
COMMAREA with the name of a temporary storage queue.

2. Read the temp storage queue that will specify the WHERE clause values
for the SELECT statement

3. Include an SQLCA

4. Load the input SQL statement into a data area

5. Declare a Cursor

6. Prepare the Select Statement

7. Open Cursor

8. Alloate space (Getmain ??) for the result table

9. Fetch Rows from the result table (I need to fetch all the rows by
going through a loop until there are no-more-rows-to-fetch). I need to
write all the rows fetched (in one chunk) to another temp storage queue
and then return to CICS

10. Close Cursor

11. Handle any error/return codes from DB2

12. My program will be started 100s of times simultaneously for the
purpose of stress testing DB2 access.

From the above, I may give out the impression that I already know what
to do. I basically pulled these out from the Application Programming
and SQL Guide. I need a practical example of how to do the above.

If anybody can provide me with some sample code that does more or less
what I need to do (in Assembler language), it will help me a lot.


--

Thanks!
Mona Syed
JCPenney Systems Architecture/CICS