I have a file that roughly has about 2.5million records for a given month. This file is used heavily to process our month ends. Currently it's reading through this file and checking for specific conditions to process month end.

In an effort to speed this job, which of these solutions are the most efficient?

1. Create a new logical with select/omit creteria
2. Create a temporary view run our month end jobs and drop the view

------------------------------
Karthik Krishnappa
------------------------------

#SQL

A view doesn't improve performance, it only makes your program easier to code as you don't need to include a complex SQL statement.  The fact that a view externalizes the SQL also makes maintenance easier (you can change the view without recompiling your program).

In order to speed up processing you need to create the correct SQL indexes... Visual Explain (part of Run SQL Scripts) can help you with this.

I would not recommend logicals (with select/omit) anymore... I suggest to go "all SQL".

------------------------------
Paul Nicolay
------------------------------

I agree with Paul.
I'll add that beyond VE, you or your team can also look at the Index Advisor, the statistics of any existing indexes (or keyed logical) over the file, and now also the MTIs over the file (via MTI_INFO).
If you don't have someone in the role of Database Engineer, you could leverage Kent Milligan from IBM Lab Services to assist in either this specific task, or with database engineering education.
Best regards, Scott

------------------------------
Scott Forstie
------------------------------

Original Message:
Sent: Thu July 14, 2022 02:07 AM
From: Paul Nicolay
Subject: SQL View

A view doesn't improve performance, it only makes your program easier to code as you don't need to include a complex SQL statement.  The fact that a view externalizes the SQL also makes maintenance easier (you can change the view without recompiling your program).

In order to speed up processing you need to create the correct SQL indexes... Visual Explain (part of Run SQL Scripts) can help you with this.

I would not recommend logicals (with select/omit) anymore... I suggest to go "all SQL".

------------------------------
Paul Nicolay
------------------------------

I'm guessing that this month-end process uses a lot of native record-level access. As Paul points out, you could go "all SQL" by creating an SQL Index with a WHERE clause to create the SQL equivalent of a Select/Omit logical.  That would provide the performance boost you're looking for in the short-term and then long-term you could migrate some of the native record-level access to SQL>

------------------------------
Kent Milligan
------------------------------

Original Message:
Sent: Thu July 14, 2022 02:07 AM
From: Paul Nicolay
Subject: SQL View

A view doesn't improve performance, it only makes your program easier to code as you don't need to include a complex SQL statement.  The fact that a view externalizes the SQL also makes maintenance easier (you can change the view without recompiling your program).

In order to speed up processing you need to create the correct SQL indexes... Visual Explain (part of Run SQL Scripts) can help you with this.

I would not recommend logicals (with select/omit) anymore... I suggest to go "all SQL".

------------------------------
Paul Nicolay
------------------------------

Thanks for all the wonderful response.

------------------------------
Karthik Krishnappa
------------------------------

Original Message:
Sent: Thu July 14, 2022 09:54 AM
From: Kent Milligan
Subject: SQL View

I'm guessing that this month-end process uses a lot of native record-level access. As Paul points out, you could go "all SQL" by creating an SQL Index with a WHERE clause to create the SQL equivalent of a Select/Omit logical.  That would provide the performance boost you're looking for in the short-term and then long-term you could migrate some of the native record-level access to SQL>

------------------------------
Kent Milligan
------------------------------


Original Message:
Sent: Thu July 14, 2022 02:07 AM
From: Paul Nicolay
Subject: SQL View

A view doesn't improve performance, it only makes your program easier to code as you don't need to include a complex SQL statement.  The fact that a view externalizes the SQL also makes maintenance easier (you can change the view without recompiling your program).

In order to speed up processing you need to create the correct SQL indexes... Visual Explain (part of Run SQL Scripts) can help you with this.

I would not recommend logicals (with select/omit) anymore... I suggest to go "all SQL".

------------------------------
Paul Nicolay

Hey Kent, question for you.

Why can't we use a subselect when creating am index, sure would be handy.

CREATE INDEX mm4r6lib.invaudix1
ON mm4r6lib.invaud (itrhot asc, itrtyp asc, itrloc asc, istype asc)
WHERE itrhot = '1'
AND itflag <> 'P'
AND itrtyp IN (SELECT tblval
FROM t1mm4r6ctl.tblfld
WHERE tblnam = 'RCSTYP'
AND tbldef = 'N') RCDFMT rinvaud ADD ALL COLUMNS;

------------------------------
James King
------------------------------

Original Message:
Sent: Thu July 14, 2022 09:54 AM
From: Kent Milligan
Subject: SQL View

I'm guessing that this month-end process uses a lot of native record-level access. As Paul points out, you could go "all SQL" by creating an SQL Index with a WHERE clause to create the SQL equivalent of a Select/Omit logical.  That would provide the performance boost you're looking for in the short-term and then long-term you could migrate some of the native record-level access to SQL>

------------------------------
Kent Milligan
------------------------------


Original Message:
Sent: Thu July 14, 2022 02:07 AM
From: Paul Nicolay
Subject: SQL View

A view doesn't improve performance, it only makes your program easier to code as you don't need to include a complex SQL statement.  The fact that a view externalizes the SQL also makes maintenance easier (you can change the view without recompiling your program).

In order to speed up processing you need to create the correct SQL indexes... Visual Explain (part of Run SQL Scripts) can help you with this.

I would not recommend logicals (with select/omit) anymore... I suggest to go "all SQL".

------------------------------
Paul Nicolay

I don't seeing that happening anytime soon. 

You're missing the point that an index exists just to possibly speed up data retrieval  & sorting of a query, not to perform additional queries.

Instead of trying to build an SQL index with an embedded query and open it via Native Record Level access, you should just change your program to run the SQL query that's defined on the WHERE clause.  Then, you could just create indexes over INVAUD & TBLFLD using the supported syntax and I think you'll find the performance pretty acceptable.
 






------------------------------
Kent Milligan
------------------------------

Original Message:
Sent: Thu July 14, 2022 10:59 AM
From: James King
Subject: SQL View

Hey Kent, question for you.

Why can't we use a subselect when creating am index, sure would be handy.

CREATE INDEX mm4r6lib.invaudix1
ON mm4r6lib.invaud (itrhot asc, itrtyp asc, itrloc asc, istype asc)
WHERE itrhot = '1'
AND itflag <> 'P'
AND itrtyp IN (SELECT tblval
FROM t1mm4r6ctl.tblfld
WHERE tblnam = 'RCSTYP'
AND tbldef = 'N') RCDFMT rinvaud ADD ALL COLUMNS;

------------------------------
James King
------------------------------


Original Message:
Sent: Thu July 14, 2022 09:54 AM
From: Kent Milligan
Subject: SQL View

I'm guessing that this month-end process uses a lot of native record-level access. As Paul points out, you could go "all SQL" by creating an SQL Index with a WHERE clause to create the SQL equivalent of a Select/Omit logical.  That would provide the performance boost you're looking for in the short-term and then long-term you could migrate some of the native record-level access to SQL>

------------------------------
Kent Milligan


Original Message:
Sent: Thu July 14, 2022 02:07 AM
From: Paul Nicolay
Subject: SQL View

A view doesn't improve performance, it only makes your program easier to code as you don't need to include a complex SQL statement.  The fact that a view externalizes the SQL also makes maintenance easier (you can change the view without recompiling your program).

In order to speed up processing you need to create the correct SQL indexes... Visual Explain (part of Run SQL Scripts) can help you with this.

I would not recommend logicals (with select/omit) anymore... I suggest to go "all SQL".

------------------------------
Paul Nicolay