Wednesday, September 10, 2014

Accessing multiple member files in SQL

How to handle multi-member files using create alias and drop alias

I was recently asked the following question, via Facebook:

How we can read a particular member of physical file thru SQL EXEC in RPGLESQL program ?

Others suggested using "Override with Data Base File" (OVRDBF) command, which could be used. But there is a way to do this just in SQL.

In this example I have a file, TESTFILE, which has two members that contain a different number of records:

Member Records
First 3
Second 2

I need to determine the number of records/columns there are in member SECOND.

The way to access different members in a multiple member file using SQL is to use ALIAS. An ALIAS is a temporary rename of a table. I always create ALIASes in QTEMP, as it cannot interfere with other jobs. With a multimember scenario like this I always name the member _, e.g. QTEMP/TESTFILE_MEMBER.

As the person posing the asked how to do this in a SQLRPGLE creating the alias would look like this:

02  exec sql CREATE ALIAS QTEMP/TESTFILE_SECOND
               FOR TESTFILE(SECOND) ;

Or I could use a variable name for the name of the member:

   Member = 'SECOND' ;
   Text = 'CREATE ALIAS QTEMP/TESTFILE_' + %trimr(Member) + 
            '  FOR TESTFILE(' + %trimr(Member) + ')' ;
   exec sql EXECUTE IMMEDIATE :Text ;

When you have finished using the ALIAS you have delete it. That is done using the DROP ALIAS, see below:

04  exec sql DROP ALIAS QTEMP/TESTFILE_SECOND ;

To perform the count of the number of records/rows in my ALIAS I am going to use a SELECT:

   SELECT COUNT(*) INTO :Count FROM QTEMP/TESTFILE_SECOND

I can put this all together like this:

01  dcl-s Count int(10) ;

02  exec sql CREATE ALIAS QTEMP/TESTFILE_SECOND
               FOR TESTFILE(SECOND) ;
03  exec sql SELECT COUNT(*) INTO :Count
                    FROM QTEMP/TESTFILE_SECOND ;
04  exec sql DROP ALIAS QTEMP/TESTFILE_SECOND ;

05  dsply Count ;
06  *inlr = *on ;

On line 2 I create my ALIAS.

I execute my SELECT statement to put the count of the records into the variable Count on line 3.

As I am done with the ALIAS I use the SQL DROP statement to delete it, line 4.

And finally I display the count of records in the member using the RPG DSPLY operation.

If you do not have the RPG all free PTFs then the equivalent definition specification would be:

01  D Count           S             10I 0

This is a very simple example of how to use the ALIAS to access a member in a multi member file. I am sure you can find more useful applications for this.

 

You can learn more about these from the IBM web site:

 

This article was written for IBM i 7.1.

5 comments:

  1. I like the approach...now how about wanting to process records from *ALL members in SQL? Obviously one could extract a list of members and loop through them using either OVRDBF or your ALIAS approach, it's a shame neither work with *ALL. Not that I have a particular application needing that option, it's just your thought provoking solution that's got me thinking...Mike

    ReplyDelete
  2. It may hit the performance... If you create alias in qtemp every time. Better create a permanent alias object or ovrdbf would be good option.

    ReplyDelete
  3. This is okay if the file has few members. I have a practical case, particular file consists of more than 1000 members.

    ReplyDelete
    Replies
    1. 1,000 members! That is ridiculous. Really bad database design.

      SQL was never designed to work with multi member files, alaises allow us to do so but they are not as neat as native I/O methods when handling them.

      Delete
    2. I had a need to read hundreds of members within SQL. I was writing a script to scan all source code in a library for a character string. I used the ALIAS option to build a list of all members and loop through them.

      Delete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.