Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Reading all the records from a database and storing in a TS

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> CICS
View previous topic :: :: View next topic  
Author Message
Roshan Khandelwal

New User


Joined: 03 Sep 2008
Posts: 4
Location: New-delhi

PostPosted: Mon Sep 08, 2008 9:53 am    Post subject: Reading all the records from a database and storing in a TS
Reply with quote

Hi.
I have to read all the records from a database and store it in a temporary queue .
I would then need to read the records from this queue one by one and put them up on the screen...
Its utterly important that i use a queue... so cannot bypass it..
Anyways i am using a cursor to read records from the database.. and its not read one by one but in a bulk.
Can anyone help..
Back to top
View user's profile Send private message

Krishna Velamur

New User


Joined: 22 Aug 2008
Posts: 22
Location: Hyderabad

PostPosted: Mon Sep 08, 2008 3:04 pm    Post subject: Reply to: Reading all the records from a database and storin
Reply with quote

Hi Roshan,
Generally we use TSQ when there are repeated lines in the screen( array).

For example there are three fields that are repeating 10 times namely Field-1, Field-2, Filed-3.

Screen looks like this

Field-1 Field-2 Field-3
------- ------- --------
------- ------- --------
------- ------- -------- (repeated 10 times)

Now declare a working storage structure.

01 W-ARRAY.
05 W-ARRAY-1 OCCURS 10 TIMES.
10 W-FIELD-1 PIC X(5).
10 W-FIELD-2 PIC X(5).
10 W-FIELD-3 PIC X(5).

Now get the data from the table using multi fetch cursor (10 rows at a time). Move the values in this structure.

For every ten rows fetch increment W-CUR-PAGE and write into TSQ.

EXEC CICS WRITEQ TS QUEUE(INQUEUE)
FROM(W-ARRAY)
LENGTH(LENGTH OF W-ARRAY)
ITEM(W-CUR-PAGE)
RESP(COMMAND-RESPONSE)
END-EXEC.

After writing the data, you can read from TSQ and display it.

EXEC CICS READQ TS QUEUE(INQUEUE)
INTO(W-ARRAY)
LENGTH(LENGTH OF W-ARRAY)
ITEM(W-CUR-PAGE)
RESP(COMMAND-RESPONSE)
END-EXEC.

Move W-ARRAY values into corresponding field values and send the map.
Hope this helps.
Back to top
View user's profile Send private message
Roshan Khandelwal

New User


Joined: 03 Sep 2008
Posts: 4
Location: New-delhi

PostPosted: Mon Sep 08, 2008 5:14 pm    Post subject: Reply to: Reading all the records from a database and storin
Reply with quote

Thanks krishna..
i would declare a cursor, then i would open it right? ..
Now when i open this cursor, i guess the data would be read in from the table..
Now i need to move all of those data into the TS queue..
i tried this

Code:
Exec sql
      declare get-details cursor for
      select   flight_number , num_of_seats , flight_code
      from     flight_details
   end-exec.

After this in a later part

Code:
exec sql
      open get-details
   end-exec.

Now i tried this

Code:
 exec cics
      writeq ts qname(flightdetail)
                    from (get-details)
                    numitems(count)    ** count is arleady declared in working-storage

   end-exec


I thought this would transfer all the details into the queue, since the cursor holds all the data...
However this shows an error "get-details" not defined as a data type.
Please help ...
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1226
Location: Israel

PostPosted: Mon Sep 08, 2008 6:11 pm    Post subject: Reply to: Reading all the records from a database and storin
Reply with quote

Typically, the SELECT within the DECLARE CURSOR should have a WHERE statement, unless you plan to read the whole table.

Roshan Khandelwal wrote:
and its not read one by one but in a bulk

The OPEN does not read any data. Once the cursor is open, you have to FETCH the rows, one by one.
Move the lines one at a time to the table in WS, every 10 lines do a WRITEQ TS.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Sep 08, 2008 11:06 pm    Post subject:
Reply with quote

Hello,

Quote:
Its utterly important that i use a queue...
I simply must ask. . .

What defined this criticality? What business requirement does it support?

What is to be done with the data once it is "on the screen"?
Back to top
View user's profile Send private message
Roshan Khandelwal

New User


Joined: 03 Sep 2008
Posts: 4
Location: New-delhi

PostPosted: Tue Sep 09, 2008 2:47 pm    Post subject: Reply to: Reading all the records from a database and storin
Reply with quote

Well,
i guess like many of us , you would have been a student at some time maybe a trainee..
When they give you case studies and expect you to follow them.
Well i am in a similar situation and so
usage of queues is critical..

And by the way using a queue is also the most efficient method.
Just for support of what i said ....

Quote:
A final approach is to write the contents of a cursor-controlled result table created at the beginning of the browse operation to CICS temporary storage. This is a simple and straightforward approach.


source : Murach's CICS for the COBOL programmer
Chapter 17 - How to work with DB2 databases
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Sep 09, 2008 3:01 pm    Post subject:
Reply with quote

Quote:

A final approach ...


does not necessary mean the only approach. Mis-quoting is political manuevering.
Please don't quote stuff that many of have read, years ago...and know how to interpret.

the number of potential queue items is an integeral part of determining the process method.
If you generate 10,000 que items
1) you might receive a visit from your cics support people
and
2) who the hell can process 10,000 items or 1,000, or 500 in a day - week?

If you want to store something, store the keys.

you want to write this stuff because you were told to do it that way, great.
I make my money consulting because most people do not think outside the box.
we don't care, it is your shop, not ours.
Many posters only try to pass on information that they have gained by experience.
Back to top
View user's profile Send private message
revel

Active User


Joined: 05 Apr 2005
Posts: 135
Location: Bangalore/Chennai-INDIA

PostPosted: Tue Sep 09, 2008 4:28 pm    Post subject:
Reply with quote

Hello,

Quote:
have to read all the records from a database and store it in a temporary queue .
I would then need to read the records from this queue one by one and put them up on the screen...
Its utterly important that i use a queue... so cannot bypass it..
Anyways i am using a cursor to read records from the database.. and its not read one by one but in a bulk.



Quote:
A final approach is to write the contents of a cursor-controlled result table created at the beginning of the browse operation to CICS temporary storage. This is a simple and straightforward approach.


Yes, Its True By using TSQ we can simlify programming technique..

Anyhow to Make use of TSQ

Fallow these steps,

1. Declare cursor
2. Open cursor
3. Fetch cursor
- mean it will read record(tuple) put it in loop ie No more Records
4. Map fethed information into TSQ
5. Write TSQ
6. Close Cursor - once end of No more record were reached

The above code will only WRITE Information to TSQ

Implement logic for SCROLL using that TSQ


Code:
Note
 
   exec sql
      open get-details
   end-exec.

Internally it will create one temperuary memory and will execute Declare cursor statements(which was decalred in either in Working storage se ction or Procedure division) and dumps results into temperuary buffer.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Sep 09, 2008 7:52 pm    Post subject:
Reply with quote

Hello,

Quote:
When they give you case studies and expect you to follow them.
When you post a question about school work, you need to mention this at the beginning. The responses for a class exercise may be very diffeent from the responses to a real-world application.

Quote:
A final approach is to write the contents of a cursor-controlled result table created at the beginning of the browse operation to CICS temporary storage. This is a simple and straightforward approach.
As DBZ mentioned, you are trying to "stack the deck". . .

Quote:
And by the way using a queue is also the most efficient method.
Sorry, but no it is not (not the way you describe - if at all). Your mis-use of the quote doesn't support this either. . .
Back to top
View user's profile Send private message
Roshan Khandelwal

New User


Joined: 03 Sep 2008
Posts: 4
Location: New-delhi

PostPosted: Fri Sep 19, 2008 4:03 pm    Post subject:
Reply with quote

Hi,
anyways i have the problem solved. Thanks for all the help.

I declared a cursor , opened it .
Fetched one record into the Working storage variables , then from there into the queue.
This continued as a loop , till all records were read into the queue ( use of SQLCODE 100) .
I then closed the cursor.

Second Phase:
I had declared a map, whose symbolic part i declared myself, rather than working with the system generated symbolic map. This allowed me to take advantage of the "OCCURS "clause .
After that i read records from the queue into the map variables referencing them one by one using the subscripts.
Hence Problem Solved.

Anyways reading was done with the help of item number option.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> CICS All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Problem reading GTF trace output danik56 ABENDS & Debugging 7 Thu Mar 16, 2017 1:02 pm
No new posts Join giving more records than expected Danielle.Filteau SYNCSORT 2 Thu Mar 09, 2017 3:28 am
No new posts TSO or command line utility to genera... kishpra JCL & VSAM 3 Thu Mar 09, 2017 1:11 am
No new posts Extract set of records matching on ac... bhaskar_kanteti DFSORT/ICETOOL 3 Mon Mar 06, 2017 7:19 am
No new posts Inserting records based on conditions vickey_dw DFSORT/ICETOOL 9 Wed Feb 22, 2017 1:33 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us