MVSFORUMS.com

sumithar · Beginner Joined: 22 Sep 2006 Posts: 84 Topics: 29

At this time we have an internal table (OCCURS clause) that we use to initially load some reference data from a DB2 table and then use for processing in the program.

Would it make sense to use a non-persistent MQ queue instead of this internal table? SELECT all rows from the DB2 table in the INIT Para and populate each row into the queue as a message.

The reason is, recently we had a table overflow situation and didn't want to limit ourselves to whatever the OCCURS limited us to!

Thanks

Cogito-Ergo-Sum · Posted: Thu Jun 12, 2008 3:02 pm Post subject:

VSAM didn't help ?

MQ would requires some more 'investment'.
_________________
ALL opinions are welcome.

Debugging tip:
When you have eliminated all which is impossible, then whatever remains, however improbable, must be the truth.
-- Sherlock Holmes.

Bill Dennis · Posted: Thu Jun 12, 2008 3:43 pm Post subject:

You could write them into a flat file, close it and then open it for input and read it back in.
_________________
Regards,
Bill Dennis

Disclaimer: My comments on this foorum are my own and do not represent the opinions or suggestions of any other person or business entity.

hari_uss · Posted: Thu Jun 12, 2008 5:48 pm Post subject:

Or use OCCURS...DEPENDING ON?

sumithar · Beginner Joined: 22 Sep 2006 Posts: 84 Topics: 29

Thanks all-
We want to minimise i/o so flat file or VSAM are kind of ruled out. Ditto for global temporary DB2 table also.
OCCURS DEPENDING ON still needs an upper limit to be specified which violates the principle of the exercise. Of course we could make this very large and might end up using this option eventually.

MQ is already in place, so no addnl expense envisaged for that and there is a corporate initiative to use MQ- hence the original question.

jsharon1248 · Posted: Fri Jun 13, 2008 8:20 am Post subject:

I don't know a lot about MQ, but it seems to me that once you externalize the data, you're talking IO. MQ is storing the data somewhere, so you're really just trading VSAM IO for MQ IO. I would hesitate to utilize a messaging technology as a temp file for a program. Consider using RRDS or KSDS VSAM. Allocate a large number of buffers in the JCL and IO shouldn't really be a problem.

One other thought. If you're going to be investing in a coding change to develop a solution, take a second look at the SQL. It seems to me that there is probably a way to make DB2 do more of the work rather than creating temp tables in a program.

Bill Dennis · Posted: Fri Jun 13, 2008 2:57 pm Post subject:

You might ask around and see if you have any VIO devices on your system. These are virtual DASD devices so there is no I/O. The records are stored in memory so if you abend, the data is gone. Usually recommended for small files with short life spans. The JCL looks like any other temporary DASD file except for the special UNIT name.

You could WRITE to VIO and then READ them back and never do real DASD I/O. If you try to write millions of records, you could strain the available real memory and cause I/O to system paging files.
_________________
Regards,
Bill Dennis

Disclaimer: My comments on this foorum are my own and do not represent the opinions or suggestions of any other person or business entity.

sumithar · Beginner Joined: 22 Sep 2006 Posts: 84 Topics: 29

sharon,
non-persistent queues are memory resident so even if "external" there should not be I/O but you raise a good point about using messaging technology for a temp file. The SQL is fairly trivial, it is the idea of re-reading the same data from the DB2 table about 200K times (the number of records in the input file being processed) that seemed avoidable

your solution about going with VSAM and using large buffering is along the same lines as Bill Dennis's recommendation of VIO devices. yes, I will definitely ask around if there are any VIO devices. This is going to be a small amount of data. Thanks Bill

jsharon1248 · Posted: Mon Jun 16, 2008 8:16 am Post subject:

sumithar

I recommend investigating a DB2 solution a little deeper. DB2 is pretty good about buffering frequently accessed data. You mention that it's a small amout of data, so DB2 is probably already buffering most, if not all, of the data in the table. If you have access to a monitoring tool, you can check the buffer hit ratio to see how often your program is actually performing IO. If not, ask a DBA to monitor a run. To me, it doesn't seems like the effort and added complexity are justified to eliminate 200K SELECTS.

Another technique to eliminate SELECTs for lookup data is to check the DCLGEN fields to see if the row required is already there. You can skip the SELECT altogether if the row is already in WS.

sumithar · Beginner Joined: 22 Sep 2006 Posts: 84 Topics: 29

Interesting suggestions- especially the one about looking at DCLGEN- didn't know about that at all. The buffering concept is similar to the addition of buffers in the DD stmt- something that I should have thought about.

I had the same discussion sparked on a forum I discovered after I posted on this (mqseries.net) and someone on that said the same thing as you- trying to avoid SQL calls is old school in today's high performance tuned DB2 world.

Thanks!