Webserver SQL SELECT statement to AS400 via ODBC question

[abacuscorp]

Good day all:

For starters, I’m not an SQL programmer. I have an inventory database file on a client’s as400 that is accessed within the building via an old RPGII program from the System/34 days. It is also accessed externally via a SELECT SQL statement on a web server via ODBC. It is externally defined for SQL, but is internally defined in the RPGII program. Converting the RPGII program is not an option, nor is it the cause of the problem I had.

A seldom used program in the system inserted a blank into a numeric field in one product record in the file, say PRODUCTX. The next occurrence of a SELECT statement for a product, say PRODUCTA, caused an error. It worked fine after I changed the blank to a number.

Observing this I came to believe that a SELECT statement must “touch” every record in the file in order to satisfy the selection criteria. This is a small 15,000 record file and the website response time hasn’t been awful. Is this observation correct? If so, what do people do with large files? Would creating a logical over the physical, then have SQL query the logical solve this problem and improve response time? SETLL & READ just looks at the target product records.

All advice would be appreciated, thank you

Warren

 

[arrow483]

For best performance, you want a logical (or index) over the file on the same fields and same sequence as used by the "where" clause. You still run the select over the physical, but SQL will automatically use the index if its available. If its not avaiable, SQL runs a table scan over the entire file to find the selected records.

 

[abacuscorp]

Thanks arrow483 for the reply.

I built a logical over the physical as you suggested and came up with the same result. It read each record in the file. Any suggestions?

Warren

 

[MdnghtPgmr]

arrow483,

Because his SQL is external to the iSeries is that true? Wouldn't he need to change the SQL to "see" the logical?

abacuscorp,

Have you tried that?

HTH,
MdnghtPgmr

 

[Mercury2]

Hi,
How do you know SQL reads each record in the physical file ?

 

[iSeriesCodePoet]

To make sure you are actually using that new logical, follow my faq in the faq section here. That will show you how to determine if SQL is actually using your logical or not.

iSeriesCodePoet
iSeries Programmer/Lawson Software Administrator

http://www.koldark.net

See my progress to converting to linux.

http://linux.koldark.net

 

[abacuscorp]

Talkturkey:

I inserted an error in a product record not assocated with the product in the SELECT statement and got an error message. I removed the error and everything was OK. This lead me to believe that records for products not mentioned in SELECT were being accessed.

Warren

 

[abacuscorp]

Iseriescodepoet

SQL on the 400 expired months ago so I can't STRSQL. The SQL SELECT statement is executed on the remote webserver. I haven't any idea how the 400 interprets what it gets from the internet. Any ideas? Thanks

Warren

 

[iSeriesCodePoet]

Hmm... I don't recall the exact job name, but you should find a job that is created when you connect. You might be able to do a batch debug on it and get the same results. I am not 100% sure. it might be worth a try.

iSeriesCodePoet
iSeries Programmer/Lawson Software Administrator

http://www.koldark.net

See my progress to converting to linux.

http://linux.koldark.net

 

[Mercury2]

abacuscorp,

I had noticed you inserted an error in a product record to determine you read the entire file but you do not definitely know if you read the entire file in this manner. I think however the program reads the entire file since you launch the SQL request to a PF.

You built a logical over this PF but did you change the program to read this logical instead ?

With large files people generally create RPG, SPL ... stored procedures on server side that access data. If there are huge files these SP's manage the necessary records little by little.
Hope I could shed some light on this.

 

[abacuscorp]

talkturkey/iseriescodepoet

I've looked (wrkactjob) when the server connected to the 400 but could never find any trace of it. As far as the logical is concerned I did not change the SQL on the server to go after the logical instead of the physical. What really seems strange is that the physical is keyed with the same field names and order of the SELECT statement. I just can't believe that OS/400 would process all the records when keys are available. Thanks for your suggestions, let me know if you think of anything else that I might try.

Warren

 

[Mercury2]

Hmm ... Give it a try with
RGZPFM Physical KEYFILE(Logical).

 

[abacuscorp]

talkturkey

Thanks for your "RGZPFM Physical KEYFILE(Logical)" reply, but I don't know what you mean. Would you please elaborate?
Thanks

Warren

 

[Mercury2]

Apologies. To clarify my previous post ) try out the following command then check if you get any SQL time improvement:
RGZPFM FILE(LIBRARY/PHYSICAL) KEYFILE(LIBRARY/LOGICAL)
This command will remove deleted records and sort out the physical file records on the logical new file keys.
The physical records are likely not physically sorted on the declared keys be they declared in this very PF or the underlying LF. Therefore, it seems pretty sure that SQL should be searching for the required criteria in all the unsorted PF records to perform the request.
hth
Philippe --