Variable file name

[ga]

I am in the process of writing a program that will check a large
number of files (90 or so) for the existence of an mfr/item #. Most
of the files I'm checking have logical files over them indexed by
mfr/item.

My question is, can I use dynamic sql to "chain" and see if that
particular mfr/item# exists in the 90 files by using a variable? I'd
like to simply do a loop 90 times and chain to the next file in
sequence and see if I get a chain or not. I am assuming I can do
something like this with dynamic SQL? I have a similar program that
uses dynamic SQL and goes through these same files and actually
changes a mfr/part # but right now I simply want to chain and see if
it finds a record. How do I chain using SQL and do I have to check
the return code or something? I am somewhat interested in speed, so
am thinking it would be better to just "chain" to each of the 90 files
and use logical files with the same key attributes. Can I just use
SQL to do a "chain" with a different file name and return whether
record is found or not???

Would anyone be willing to share a brief example of how I might do
this?

Thanks,
ga
-----------------------------------------------------------------------------------------------------------------
Here is the dynamic SQL statement I am using to change mfr/part # now.
I am not enough of an SQL guru to know what I need to change to just
make it more of a "check to see if the record might exist"
routine...now I just want to simply do a "check FILE for existence of
MIOLD and PNOLD where in the particular file PNFLD and MIFLD are the
corresponding fields to check. I don't want to UPDATE anything, I just
want to know if a record is in the file the matches the mfr/item#...

C**********************************************************
C EVAL statement = 'Update ' + %trim(FILE) +
C ' set ' + %trim(MIFLD) + ' = ''' +
C %trim(MINEW) + ''', ' + %trim(PNFLD) +
C ' = ''' + %trim(PNNEW) + ''' where ' +
C %trim(MIFLD) + ' = ''' + %trim(MIOLD) +
C ''' AND ' + %trim(PNFLD) + ' = ''' +
C %trim(PNOLD) + ''''
C**********************************************************
C/EXEC SQL
C+ SET OPTION COMMIT=*NONE
C/END-EXEC
C/EXEC SQL
C+ EXECUTE IMMEDIATE :STATEMENT
C/END-EXEC
C/EXEC SQL WHENEVER SQLERROR CONTINUE
C/END-EXEC
C MOVEL SQLCODE PRTCOD
C MOVEL SQLSTATE PRTSTA
C**********************************************************
ga
nos...@nospam.fmctc.com

[Roy Øyan]

A simple Select statement will do the job, check returncode (SQLCODE)
after the select.

Select "some field" from "file" where item#="Your value"
I SQLcode = 0 ther exist one.
Mark, if there is multiple occurenses Sqlcode return a different
value, google sqlcode and you can find what values that you need

[Jonathan Ball]

On 5/17/2012 9:11 AM, ga wrote:
> I am in the process of writing a program that will check a large
> number of files (90 or so) for the existence of an mfr/item #. Most
> of the files I'm checking have logical files over them indexed by
> mfr/item.
>
> My question is, can I use dynamic sql to "chain" and see if that
> particular mfr/item# exists in the 90 files by using a variable? I'd
> like to simply do a loop 90 times and chain to the next file in
> sequence and see if I get a chain or not. I am assuming I can do
> something like this with dynamic SQL? I have a similar program that
> uses dynamic SQL and goes through these same files and actually
> changes a mfr/part # but right now I simply want to chain and see if
> it finds a record. How do I chain using SQL and do I have to check
> the return code or something? I am somewhat interested in speed, so
> am thinking it would be better to just "chain" to each of the 90 files
> and use logical files with the same key attributes. Can I just use
> SQL to do a "chain" with a different file name and return whether
> record is found or not???
>
> Would anyone be willing to share a brief example of how I might do
> this?
>
> Thanks,
> ga
> -----------------------------------------------------------------------------------------------------------------

> [...]

Do you need to take some course of action for each file in which the
mfr/item# exists, or do you only need to know if the value combination
exists in any of the files?

If you want to take some course of action each time you detect the value
combination in a file, then using your dynamic SQL example, you could
successively declare a cursor over each table, open the cursor, attempt
to fetch one row from it, and check the SQL code or state. If a row was
returned, do one thing, else do some other thing. Close the cursor,
then repeat for each of the remaining files.

If you just need to know if the value combination exists in any (at
least one) file, then you could code a really ugly massive statement
like this:

select '1'
into :some_indicator
from sysibm/sysdummy1
where exists
(select 'Y'
from file_1
where mfr_id = :rpg_mfr_var
and item# = :rpg_item_var)
or exists
(select 'Y'
from file_2
where mfr_id = :rpg_mfr_var
and item# = :rpg_item_var)
.
.
.
or exists
(select 'Y'
from file_90
where mfr_id = :rpg_mfr_var
and item# = :rpg_item_var)

If the variable is on, then the value combination exists in at least one
of the files; if it's off, then it doesn't exist in any of them. If you
know all 90 file names in advance, then you could code this as a static
statement; if not, then you would have to declare a cursor over a
prepared statement, prepare the SELECT statement dynamically, then open
the cursor and attempt to fetch the value into the indicator variable.
Admittedly, the statement is ugly, but then so is having a database
architecture with 90 files containing essentially the same data.

[ga]

Jonathan,

Well, the database might be ugly but then again, maybe not as bad as
you think... :-) :-) I have lots of files that reference my
ITEMMAST and the key is MFR/ITEM. I'm not sure how I could design the
files but anyway, that's a different can of worms.

Here is my EVAL clause I am working with now:

C**********************************************************
C EVAL statement = 'Select * '+
C 'From ' + %trim(FILE) +
C ' where ' +

C %trim(MIFLD) + ' = ''' + %trim(MIOLD) +
C ''' AND ' + %trim(PNFLD) + ' = ''' +
C %trim(PNOLD) + ''''
C**********************************************************

I don't need to do a super long thing like you suggested because I at
least put the FILE, MIFLD and PNFLD into arrays so I just simply loop
through them and sub in the array element data. All I want to know is
if any record has a match with the MIOLD and PNOLD fields I am
checking.

Your example is helpful except you lost me at sysdummy1 - I'd like you
to know, yes, I do "resemble" that remark...by the way.

In reading your code, it appears I need to modify my EVAL something
like:

SELECT '1' (is this forcing an indicator to on if it gets a match?)
from SYSIBM/SYSDUMMY1 (what is this?????)
into:*IND11
From FILE
WHERE exists (what is this doing?)
select 'Y' (what is this doing)
where
mifld = miold
pnfld = pnold

At the end, of each loop, if I test *IND11 and if it's on, then I know
I had a match and that's all I need to know. If I get a match in the
first file, or second, or fifteenth I don't need to go any further.
Otherwise, I loop through and force new variables into FILE, MIOLD and
PNOLD.

I want to thank you for taking the time to respond to my email. And
if nothing else, I've provided you with some humor for the day, right?

Thanks!
ga

ga
nos...@nospam.fmctc.com

[Jonathan Ball]

On 5/18/2012 11:25 AM, ga wrote:
> Jonathan,
>
> Well, the database might be ugly but then again, maybe not as bad as
> you think... :-) :-) I have lots of files that reference my
> ITEMMAST and the key is MFR/ITEM. I'm not sure how I could design the
> files but anyway, that's a different can of worms.

Are these 90 files all different logical files that reference the same
underlying physical file?

>
> Here is my EVAL clause I am working with now:
>
> C**********************************************************
> C EVAL statement = 'Select * '+
> C 'From ' + %trim(FILE) +
> C ' where ' +
> C %trim(MIFLD) + ' = ''' + %trim(MIOLD) +
> C ''' AND ' + %trim(PNFLD) + ' = ''' +
> C %trim(PNOLD) + ''''
> C**********************************************************
>
> I don't need to do a super long thing like you suggested because I at
> least put the FILE, MIFLD and PNFLD into arrays so I just simply loop
> through them and sub in the array element data. All I want to know is
> if any record has a match with the MIOLD and PNOLD fields I am
> checking.
>
> Your example is helpful except you lost me at sysdummy1 - I'd like you
> to know, yes, I do "resemble" that remark...by the way.

Heh heh heh...a misunderstanding. SYSDUMMY1 is a dummy table, like DUAL
in Oracle, that you can use when you need a dummy table for a FROM
clause. In some implementations of SQL, e.g. T-SQL, you don't need a
FROM clause; you can code something like

select '1' into :host_variable
where [some selection criteria]

You can't do that in DB2. DB2 supports a VALUES or a SET statement, but
you can't specify selection criteria. To do that, you need to code a
SELECT, and to code a SELECT you need a FROM. SYSDUMMY1 is an IBM
supplied table in SYSIBM that contains a single row with a single column
(IBMREQD; has a value of 'Y'). You can select a literal you want, which
is a good way to set an indicator or some other variable based on some
condition:

select '1' into :my_indicator
where [some selection criteria]

>
> In reading your code, it appears I need to modify my EVAL something
> like:
>
> SELECT '1' (is this forcing an indicator to on if it gets a match?)
> from SYSIBM/SYSDUMMY1 (what is this?????)
> into:*IND11
> From FILE
> WHERE exists (what is this doing?)
> select 'Y' (what is this doing)
> where
> mifld = miold
> pnfld = pnold

Not quite. If you're going to loop through an array containing 90 files
and variable names (and values), then you should use a cursor, which
will allow you to simplify your SELECT statement, specifically by
getting rid of the subquery in the EXISTS clause. Your statement
variable will look (after you do your EVAL) something like this:

select '1'
from FILE
where mifld = miold
and pnfld = pnold

except that 'miold' and 'pnold' would be the values of the variables
instead.


You'd also have these:

Exec SQL
Declare Check_Mfr_Item cursor for Prepared_Cursor_Statement;


Exec SQL
Prepare Prepared_Cursor_Statement from :Statement;


Exec SQL
Open Check_Mfr_Item;


Mfr_Item_Exists = *off;


Exec SQL
Fetch Check_Mfr_Item into :Mfr_Item_Exists;


Exec SQL
Close Check_Mfr_Item;


Of course, you'd probably want to include some checks of SQLSTT or
SQLCOD for your open and fetch. Note that you only have to attempt to
fetch one row from the cursor each time. At the conclusion of an
iteration, if Mfr_Item_Exists is on, then the value exists in the file;
if it's off, then the value doesn't exist. From what you said below, if
it comes on at any point, you're done and you can end your loop.

You could also define the cursor using parameter markers, which might
simplify your EVAL statement somewhat. That would be most suitable if
the column names (mifld, pnfld) are the same in every file. Then you
could have your statement variable hardcoded something like

select '1'
from XXXXXXXXXX
where mifld = ?
and pnfld = ?


Assuming the file name placeholder XXXXXXXXXX is at position 17, you'd
then code

%subst(statement:17:10) = file_arr_elem;


Then your cursor open statement would be

Exec SQL
Open Check_Mfr_Item using :miold, :pnold;


You could do this even if the column names changed from one file to the
next, but if you're having to string the column names into the statement
variable, you might as well string the values for them in as well.

[Jonathan Ball]

Sorry; this was supposed to be

select '1' into :my_indicator
from sysibm/sysdummy1

[M]

On Thu, 17 May 2012 11:11:55 -0500, ga <noe...@spam.fmctc.com> wrote:

>I am in the process of writing a program that will check a large
>number of files (90 or so) for the existence of an mfr/item #. Most
>of the files I'm checking have logical files over them indexed by
>mfr/item.

If all the files have an identical layout, and the appropriate indexing, you should be
able to accomplish this without using SQL.

Use the keywords USROPN, EXTFILE, and EXTMBR on the file definition... and then OPEN,
CHAIN, and CLOSE the actual files as required.

--
Mark.

[ga]

Jonathan,

I tweaked my code a little based on some of your suggestions but now
I'm into another quagmire...I now get an SQL0312 error. The first
time I tried this I maybe had it 'into :GNACNT ', since I was getting
the SQL0312 error thought maybe I had to code quotes around it. Still
get the SQL0312.

What I am after is simply looping through a series of FILEs, MIFLDs
and PNFLDs and seeing if there are any records in that FILE that match
the MIOLD and PNOLD which I pass into the program as parms. Is there a
way to get around the SQL0312 error? The way I have this coded, each
time through it does the EVAL with different values for FILE, MIFLD
and PNFLD. If GNACNT is ever "1", after it does the CALSQL routine, I
know there was a match in at least one instance/iteration.

My original idea was simply to do a "Select *" (with no into) and
simply check the SQLSTATE. I assumed if the SQLSTATE was 02000 no
match, if it was 00000 a match, but apparently, because GNACNT is
considered a host variable, it returns and SQLSTATE of 43612 or
something like that. :-(

I changed my SQL to replace the "Execute immediate" with "PREPARE
SQLSTMT from :STATEMENT" but same result - SQL0312.

I admit it, I am lost.

These are the relevant pieces of my code (which rapidly seem to
becoming irrelevant, ha!):

D spec:
D GNACNT S 1

Eval statement:
C**********************************************************
C EVAL statement = 'Select' + '''1''' +
C ' into ' + ':GNACNT ' +

C 'From ' + %trim(FILE) +
C ' where ' +
C %trim(MIFLD) + ' = ''' + %trim(MIOLD) +
C ''' AND ' + %trim(PNFLD) + ' = ''' +
C %trim(PNOLD) + ''''
C**********************************************************

SQL routine:
C**********************************************************
C CALSQL BEGSR

C/EXEC SQL
C+ SET OPTION COMMIT=*NONE
C/END-EXEC
C/EXEC SQL
C+ EXECUTE IMMEDIATE :STATEMENT
C/END-EXEC
C/EXEC SQL WHENEVER SQLERROR CONTINUE
C/END-EXEC
C MOVEL SQLCODE PRTCOD
C MOVEL SQLSTATE PRTSTA

C ENDSR
C**********************************************************


ga
nos...@nospam.fmctc.com

[ga]

Unfortunately they don't have the identical layout and some are
indexed solely on the MFR/PART but some are not. That's why I was
trying to use SQL...

Thanks! ga

ga
nos...@nospam.fmctc.com

[Jonathan Ball]

On 5/21/2012 5:01 AM, ga wrote:
> Jonathan,
>
> I tweaked my code a little based on some of your suggestions but now
> I'm into another quagmire...I now get an SQL0312 error. The first
> time I tried this I maybe had it 'into :GNACNT ', since I was getting
> the SQL0312 error thought maybe I had to code quotes around it. Still
> get the SQL0312.

You're trying to use a host variable in dynamic SQL. You can't; they're
only allowed in static SQL. Specifically, it's the part of your
statement that reads "into :GNACNT".

>
> What I am after is simply looping through a series of FILEs, MIFLDs
> and PNFLDs and seeing if there are any records in that FILE that match
> the MIOLD and PNOLD which I pass into the program as parms. Is there a
> way to get around the SQL0312 error? The way I have this coded, each
> time through it does the EVAL with different values for FILE, MIFLD
> and PNFLD. If GNACNT is ever "1", after it does the CALSQL routine, I
> know there was a match in at least one instance/iteration.
>
> My original idea was simply to do a "Select *" (with no into) and
> simply check the SQLSTATE. I assumed if the SQLSTATE was 02000 no
> match, if it was 00000 a match, but apparently, because GNACNT is
> considered a host variable, it returns and SQLSTATE of 43612 or
> something like that. :-(

You will always have to SELECT, or FETCH, into something. If you do
static SQL, you can SELECT into a host variable directly in your SELECT
statement:

SELECT [expression or column]
INTO :some_host_variable
FROM [table expression]

If you use a cursor, as I was recommending, you declare your cursor
either statically:

DECLARE CURSOR my_cursor FOR
SELECT [expression or column]
FROM [table expression]

or dynamically, with a couple of other steps:

DECLARE CURSOR my_cursor FOR my_prepared_SQL_statement

EVAL sql_statement = 'SELECT...[rest of statement]'

PREPARE my_prepared_SQL_statement FROM :sql_statement


Either way, you then OPEN the cursor, and fetch a row from it INTO some
host variable(s). Since you're only testing to see if you actually
retrieved a row, you could simply check SQLSTT to see if it's '00000'
(got a row) or '02000' (didn't get a row - functionally same as EOF).
Or, if your [expression] in your SELECT statement was the value '1',
which you FETCH into an indicator you initialize to '0' (*OFF), you
could check if the indicator is on. Either way, you know the value
you're interested in exists.

This isn't going to work, because you can't do an EXECUTE - either
IMMEDIATE or from a prepared statement - to SELECT INTO a host variable.

Feel free to write at the e-mail address given; it's a valid address.

[Jonathan Ball]

Sorry, I had the cursor name and the CURSOR keyword reversed from the
proper order in the statements below. The cursor name precedes the
CURSOR keyword.

[ga]

Jonathan,

Here are the two snippets of code I finally settled on and it seems to
now be working exactly as I want:

Statement construct:
C**********************************************************
C/Free
Statement = 'Select COUNT(*) +
From ' + %trim(FILE) +
' where ' + %trim(MIFLD) + ' = ''' + %trim(MIOLD) + ''' +
AND ' + %trim(PNFLD) + ' = ''' + %trim(PNOLD) + '''';
/End-Free
C**********************************************************

SQL:
C**********************************************************
C CALSQL BEGSR
C/Free
Exec SQL Prepare DynStmt From :Statement;
If SQLCODE < *Zero;
//Handle Error
EndIf;

Exec SQL Declare Csr Cursor For DynStmt;

Exec SQL Open Csr;
If SQLCODE < *Zeros;
//Handle Error
EndIf;

Exec SQL Fetch Next FROM Csr into :CountX;
If SQLCODE < *Zeros;
//Handle Error
ElseIF SQLCODE = 100;
//Not Found
Clear CountX;
EndIf;

Exec SQL Close Csr;
/End-Free
C ENDSR
C**********************************************************

Your suggestions as well as some extremely helpful suggestions and
examples from Birgitta Hauser finally turned on the light-bulb for me.
I'm just hoping I can keep the light glimmering!!

Thanks again Jonathan for all the time you took. Getting this working
is a big plus for our company!
ga
nos...@nospam.fmctc.com

[Jonathan Ball]

On 5/24/2012 5:52 AM, ga wrote:
> Jonathan,
>
> Here are the two snippets of code I finally settled on and it seems to
> now be working exactly as I want:
>
> Statement construct:
> C**********************************************************
> C/Free
> Statement = 'Select COUNT(*) +
> From ' + %trim(FILE) +
> ' where ' + %trim(MIFLD) + ' = ''' + %trim(MIOLD) + ''' +
> AND ' + %trim(PNFLD) + ' = ''' + %trim(PNOLD) + '''';
> /End-Free
> C**********************************************************

That will work, but I *never* recommend selecting the count of rows that
meet some condition unless knowing the actual non-zero count is useful.
All you really want to know is if there is either zero or at least
one; if the answer is at least one, you don't care how many (at least,
based on what you've said.)

True story: in 2004, I worked on a big project at BCBSNC. They were
converting their group systems from mainframe to AS400. Some mainframe
COBOL programmers had written a bunch of SQLCBLLE and SQLCBL programs,
and they were performing horribly in system testing - the overnight
processing, which was supposed to complete in about a six or seven hour
window, was running more than 35 hours, and many jobs were killed
without ever ending. I was assigned to work on one that seemed to be
plowing through its data quickly until coming to a virtual standstill
about five minutes into the job; 14 or 15 hours later, they would kill
the job. I found a SELECT COUNT(*) that worked well enough when the
result was a few dozen or hundred rows, but which completely stalled
when it got into a huge block of data in which the counts were 50,000 to
80,000 - *multiple* such blocks.

I changed the code to:

select '1'
into :some-COBOL-host-variable

from sysibm/sysdummy1
where exists
(select 'Y'

from [table expression]
where [various selection criteria])

and submitted it for the next night's system test. The job ran to
completion in eight minutes. The senior project management came barging
into our area the next morning saying they didn't believe it. We were
able to prove it, and I was the Hero Of The Day. Actually, I was only
the hero for the next five minutes or so; that's just how life really goes.

I don't think I knew about FETCH FIRST n ROWS ONLY at the time; or maybe
it wasn't supported in SQL in that OS release. Today, I would do it
like this:

select '1'
into :some-COBOL-host-variable
from [table expression]
where [various selection criteria]
fetch first 1 row only

I recommend selecting some literal, e.g. '1' as I suggested earlier,
instead of COUNT(*), then change your FETCH statement and control logic
as shown below.

>
> SQL:
> C**********************************************************
> C CALSQL BEGSR
> C/Free
> Exec SQL Prepare DynStmt From :Statement;
> If SQLCODE< *Zero;
> //Handle Error
> EndIf;
>
> Exec SQL Declare Csr Cursor For DynStmt;
>
> Exec SQL Open Csr;
> If SQLCODE< *Zeros;
> //Handle Error
> EndIf;
>
> Exec SQL Fetch Next FROM Csr into :CountX;

Instead of fetching into a numeric host variable, fetch the literal '1',
as defined in the SELECT statement above, into an indicator variable:

d Row_Exists s n

/free

Exec SQL Fetch Next FROM Csr into :Row_Exists;

If SQLCODE < *Zeros;
//Handle Error

ElseIf Row_Exists; // equivalent to "Row_Exists = '1'"
// Now you can stop; do whatever
EndIf;

> If SQLCODE< *Zeros;
> //Handle Error
> ElseIF SQLCODE = 100;
> //Not Found
> Clear CountX;
> EndIf;
>
> Exec SQL Close Csr;
> /End-Free
> C ENDSR
> C**********************************************************
>
> Your suggestions as well as some extremely helpful suggestions and
> examples from Birgitta Hauser finally turned on the light-bulb for me.
> I'm just hoping I can keep the light glimmering!!
>
> Thanks again Jonathan for all the time you took. Getting this working
> is a big plus for our company!

Glad to have been of help.