Home > IT Answers > AS/400 > VSAM versus DB2 in CPU consumption
Abelmart
30 pts.
 VSAM versus DB2 in CPU consumption
z/OS CPU consumption, VSAM, AS/400 DB2, DB2
Is it possible estimate how much more CPU is consumed by DB2 commands in relation to VSAM KSDS when access a row, through an index scan, in a organized DB2 table against a record reading from an organized VSAM KSDS file through the primary index?

How much is it in percentage?

Thank you very much in advance.

Abel



Software/Hardware used:
DB2 z/OS and VSAM data management
ASKED: Sep 20, 2011  3:57 PM GMT
UPDATED: March 31, 2012  3:52:27 PM GMT
RELATED QUESTIONS
Meandyou
5,175 pts.
Answer Wiki:
Thank you all for the comments but I was not clear about my problem description.

My company has already made the decision to convert several COBOL/VSAM legacy systems to COBOL/DB2 after evaluating all their advantages and disadvantages. My job now is estimate how much we have increase the CPU to define the budget.

We have strong experience in DB2 and, through the bold DB2 configuration, reach high DB2 hit ratios, close to 100%; we have very fast DB2 applications with low CPU consumption and therefore we have hesitation in saying that, after conversion, there will be an important CPU consumption growth because with DB2, in function of its greater ability to cache than VSAM, we will certainly have lower CPU consumption of I/O operations since we today have DB2 applications with very low physical read occurrences against high occurrences of VSAM files I/O operations.

The process basically will consist of converting the Cobol/VSAM commands to SQL commands:
- Cobol OPEN <file> will be eliminated
- Cobol CLOSE <file> will be replaced by SQL CLOSE CURSOR
- Cobol START <file> will be replaced by SQL DECLARE / OPEN CURSOR
- Cobol Random READ will be replaced by SQL SELECT/INTO (eg the original question)
- Cobol REWRITE will be replaced by SQL UPDATE
- Cobol WRITE will be replaced by SQL INSERT
- Cobol DELETE will be replaced by SQL DELETE

Well, I hope this clarifies my problem then please help me find some DB2 versus VSAM CPU consumption benchmarking of similar processes.

Thank you very much.

Abel.

------

I have the same answer as before. There is no direct comparison.

One is an access method. The other is an RDBMS.

you cannot compare an OPEN VSAM FILE with OPEN DB2 CURSOR. They are not (repeat not) the same thing. The OPEN CURSOR will actually read data and get ready to deliver it to your application. It may access many, many tables in that one cursor; and join the data and filter it and sort it and perform functions on it (e.g. SUM), and transform the data (e.g. CASE), and deliver unto your program something that is not at all equivalent to a VSAM OPEN and READ.

If I even try to compare a VSAM KSDS READ with a DB2 SINGLETON SELECT (which might seem like a valid comparison) and IF the SQL did nothing other than deliver the data (no functions, no joins, no nothing) just a simple SELECT INTO, you then must take the DB2 optimizer into account and update the catalog statistics appropriately. DB2, might read all the data to find a row. Or, it might use an index to go directly to the row. Or it might use part of an index to get to the rows (note plural).

for example, if I want to total some of the records in a VSAM file. I need to read every record into my program, check if it is what i am looking for, if it is add it some counter, go read another record.

to do this in SQL I might have ONE statement like this:
SELECT SUM(some_field) as TOTAL_THESE_ROWS
FROM some_table
WHERE some_condition

Now, we know that DB2 must physically read every row. The buffer manager will have to read all the rows, but he will read them asynchronously. And while that is happening, the data manager will make a lot of the decisions to decide if this is a row (record) we want. then and only then will the field we are working with be passed to the RDS (relational data system) to be summed into the total. Once all that work is done the one field called TOTAL_THESE_ROWS will be returned to the application.

There just is no comparison. You cannot compare an access method with an RDBMS.

I truly understand your frustration. But it cannot be done with any hope of getting a meaningful result.

If you are truly replacing VSAM with DB2 and doing it "one to one" then you are maybe trying to use DB2 as an access method. And that is a waste.
For example, in your list of "comparisons" you list VSAM READ with DB2 SELECT followed by VSAM REWERITE with DB2 UPDATE. IN VSAM you gotta read it before your rewrite the changed data. In DB2, you can just UPDATE it. You do not have to read it.
But if you are replacing one VSAM operation with one DB2 operation, then you are trying to use DB2 as an access method.
And it will cost you more with DB2 than with VSAM.

This would not neccessarily be true if you use DB2 as an RDBMS.

Steve
Last Wiki Answer Submitted:  Sep 27, 2011  7:44 PM (GMT)  by  Meandyou   5,175 pts.
Latest Answer Wiki Contributors:  Abelmart   30 pts.
To see other answers submitted to the Answer Wiki View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



 

When you say DB2 commands, are you asking about SQL statements? When you ask about access a row, which DB2 statement would you use? Using either DB2 commands or VSAM KSDS, how would you measure an index scan, especially how do you mean it for VSAM?

The comparisons you’re asking about don’t make complete sense. Generally, a major point about SQL is that it operates on “sets” of data. An operation such as “access a row” isn’t really appropriate for SQL, although it can be done. But just because something can be done doesn’t mean that it should be.

VSAM is often better when specific rows need individual changes, and SQL is better when multiple rows (a “set” of rows) need the same changes. If it’s important, use the tool that is appropriate for the task.

Also, this question shows up tagged with both ‘z/OS CPU consumption’ and ‘AS/400 DB2′. Did you add those tags? Since they refer to radically different platforms, I can’t make sense of them. What are you trying to determine? Is this a question about a comparison between platforms?

Tom

TomLiotta
 66,990 pts.
 

Another point is that SQL can do so much more than just “read a record” like VSAM.
Any comparison between DB2 (SQL) and a VSAM read would be faulty.

I remember having this and similar discussions back 25-odd years ago when DB2 was coming into our shop for the first time. Many people, including me for a while, focused on things like physical I/O when trying to compare DB2 to VSAM.

DB2 is *not* an access method; VSAM is.

DB2 is a relational database management system; VSAM is not.

You must compare all other aspects of using an RDBMS.

I think the benefits of using an RDBMS over VSAM are huge, in most cases. (yes, VSAM still has place.)

Look on the bright side. VSAM KSDS is used by DB2 for his Boot Strap data Set. And the page spaces are VSAM Linear data sets. After all, some sort of file has to be used. :-)

Staff must lose conventional data processing mindset and learn relational processing. Do not replace VSAM with DB2 if you will not embrace the RDBMS and all of its functionality. Learn DB2, learn SQL, and benefit from all it can do for you.

Good luck.
Steve

Meandyou
 5,175 pts.
 

Thank you for your comments Steve,

I have not been clear about my question once we do not want compare technically DB2 and VSAM because we know both very well but there is something more than the technical analysis and my company needs now set a budget for likely growth of the CPU costs after application systems conversion from VSAM to DB2, only this.

We will go to develop two versions of the same transaction online (VSAM KSDS and DB2 versions) and we will run both in parallel some thousand times and will compare the CPU consumption.

Our expectation is that the CPU consumption difference is minimal because despite DB2 is “heavier”, its better abilities of data cache and search engines will compensate itself.

One more time, thanks.

Best regards.

Abel.

Abelmart
 30 pts.
 

The process basically will consist of converting the Cobol/VSAM commands to SQL commands:
- Cobol OPEN <file> will be eliminated
- Cobol CLOSE <file> will be replaced by SQL CLOSE CURSOR
- Cobol START <file> will be replaced by SQL DECLARE / OPEN CURSOR
- Cobol Random READ will be replaced by SQL SELECT/INTO (eg the original question)
- Cobol REWRITE will be replaced by SQL UPDATE
- Cobol WRITE will be replaced by SQL INSERT
- Cobol DELETE will be replaced by SQL DELETE

If that is the intended architecture of a majority of the programming, then VSAM is likely to be a better choice. That structure is effectively a misuse of SQL.

SQL is not intended to handle record-by-record processing even though it can be done as you suggest. Doing so causes instruction flow to continually switch between running program instructions then making calls down into DB2 routines (for each individual I/O operation) before coming back up into program instructions again.

SQL is intended to have a single SQL statement process many rows at once. That is, SQL should process a “set” of records whenever possible. That’s what it is optimized to do. The code should drop into the DB2 routines only once and stay there until finished — especially assuming that performance is the objective.

By using a cursor and progressing a row at a time, you are perhaps doing little more than adding an additional layer onto your I/O.

IMO (all of this is IMO), it might have become a biased comparison in the proposed architecture.

Tom

TomLiotta
 66,990 pts.