How to process multi-member physical files in various environments

SQL is a platform-independent database operating language. Although it cannot process physical files (tables) on iseries that contain multiple members, it is the same as RPG/Cobol, is a member of a physical file that can be written to a database file. Therefore, you can use the command ovrdbf and SQL to process multi-member physical files:

Ovrdbf file (name) tofile (toname) MBR (name) ovrs.pdf (* job)

Note the following points:
1. You can only override one member, not all Members;
2. The ovrstransform (* job) parameter ensures that override actions can be identified even if they are executed in Different Activation groups;
3. We recommend that you use the ovrssag (* job) parameter unless you want to explicitly limit the range of override.

To remove override, run the following command:

Dltovr file (name) LVL (* job)

The value of the parameter LVL is equivalent to the value of ovrs.pdf in the ovrdbf command, specifying the range of override.

The override member methods vary based on the methods you use SQL:

1. iSQL environment:

In the iSQL environment, the simplest way is to run the command ovrdbf on the 5250 command line before entering the SQL environment. If you have already entered the iSQL environment, you can also start the 5250 command line by using the following command:

Call qcmd

After the ovrdbf command is executed, press F3 to exit the 5250 command line and return to the iSQL environment. Note that if you specify the parameter ovrs1_( * job ), the system can still identify the content you want to override.

2. Embedded SQL environment:

In an embedded SQL environment, to override a member, you must use qcmdexc. For example, in Ile RPG, the prototype of qcmdexc you defined is:

D runcommand PR extpgm ('qcmdexc ')
D command _ 999 const
D size _ 15 P 5 const
D commandsize s 15 P 5 INZ (999)

Run the following override before executing the SQL statement:

/Free
Runcommand ('ovrdbf file (name) tofile (name) ''' MBR (name) ovrscope (* job) ': commandsize)
/End-free

3. ODBC/ole db environment:

3.1 Implement ovrdbf through stored procedure call

In ODBC, the command can be executed by calling the system API of the command to be run. With this technology, you can override the members you want to use with the ovrdbf command before executing SQL. For example:

Call qsys. qcmdexc ('ovrdbf file (user1) tofile (mylib/user1) MBR (company) ovrs1_( * job) ', 0000000066.00000)

In this example, 0000000066.00000 is a 10-bit/5-Bit fixed-length decimal field, and the value 66 is the length of all characters in single quotes, including spaces. Note: The remaining digits must be 0.

See the following URL for an example of Visual Basic ODBC:

Ftp://testcase.software.ibm.com/as400/fromibm/ApiSamples/

3.2 implement ovrdbf through SQL alias

The OS/400 r430 and later versions support SQL statements related to aliases. Create an alias for each member you want to access and access these aliases in ODBC to access different members. Since aliases are fixed, you only need to create them once. Any SQL tool, such as the ODBC routine mentioned in iSQL and 3.1, can be used to create these aliases. For example:

Create alias mylib. file1mbr1 for mylib. myfile (mbr1)
Create alias mylib. file1mbr2 for mylib. myfile (mbr2)

After creating different aliases, the application on the PC can specify mylib. file1mbr1 or mylib. file1mbr2 to access the members you want to access.

Notes for using aliases in ODBC:
1. The type returned by the ODBC directory function for an alias is "alias ". Some applications may not regard aliases as available table names in the database;
2. Even if the physical file does not exist, the alias can still exist independently (similar to symbolic connections );
3. There are some restrictions on the use of Alias reference members in SQL statements, specific content refer to DB2 for AS/400 SQL reference (SC41-5612)
Detailed description in the book.

References:
DB2 for AS/400 SQL reference (SC41-5612)