Working with multimember physical files using SQL

Working with multimember physical files using SQL
Working with multimember physical files using SQL, CREATE ALIAS, SQL ALIAS, DB2 ALIASES, CREATE ALIAS on IBMi, CREATE ALIAS on AS400,ALIAS, QCMDEXC, OVRDBF, override database file using SQL stored procedure QCMDEXC, ibmi, as400
Working with multimember physical files using SQL

Introduction

We can access the files with multiple members using SQL methods the same way we did in the IBM i. So, this blog will brief you on some ways to achieve this.

In IBM i when we access a multimember file, we always default be accessing the first member of a file, the same way when we execute ay INSERT, DELETE, UPDATE, and the SELECT statement on a multimember physical file the DB2 for i SQL will be using the default first member.

There are several ways to access a multimember physical file using SQL. Let's discuss.

Call Stored procedure QSYS.QCMDEXC to OVRDBF(Override database file)

Nowadays, we can easily call the IBM i system commands by calling the Execute command API stored procedure call through SQL Interface.

The same way we run the OVRDBF from the IBM i command line and to override to a specific member of a multimember physical file and then run the SQL statements over that file to refer to that particular member instead of the default first member, we can execute the same OVRDNF command from the SQL.

Let's take the example of file WEB_3 in library EASYCLASS1 having three members named WEB_3(first), MBR2(second), MBR3(last). Only the second member MBR2 has data so let's perform SQL operations on the second member MBR2.

If we execute the below SELECT query on file WEB_3 will select the default data from the first member which is empty.

select * from web_3
SQL SELECT on file WEB_3(Access default firt member), SQL SELECT in IBMi AS400, multimember physical fle in as400, ibmi
SQL SELECT on file WEB_3(Access default first member)

Once we override to the second member MBR2 using the call to the stored procedure as follows from STRSQL, then we will be set to perform SQL operations on that member MB2 instead of the default first member.

CALL                                                                
QSYS.QCMDEXC(                                                       
'OVRDBF FILE(WEB_3) TOFILE(EASYCLASS1/WEB_3) MBR(MBR2) OVRSCOPE(*JOB)', 
0000000068.00000)
Override member using SQL call to stored procedure QSYS.QCMDEXC, QCMDEXC,Execy=ute command system api in as400, OVRDBF, override database file member in as400, SQL stored procedure QCMDEXC, ibmi, multimember physical file in as400
Override member using SQL call to stored procedure QSYS.QCMDEXC

Warning!
Please note that the length passed as a second parameter to stored procedure QSYS.QCMDEXC is a fixed decimal (10,5) field and the number must be filled with zeros.

The stored procedure call is complete. Now we can check the file member override by executing command WRKJOB on IBM i command line and take option 15 for file overrides and will see the active overrides on the file in that job.

WRKJOB-option 15 (File overrides), OVRDBF,IBmi,AS400
WRKJOB-option 15 (File overrides)

If we execute the below SELECT query on file WEB_3 will now select the member MBR2 data from the second member which has data.

select * from web_3
Override member using SQL call to stored procedure QSYS.QCMDEXC, QCMDEXC,Execy=ute command system api in as400, OVRDBF, override database file member in as400, SQL stored procedure QCMDEXC, ibmi, multimember physical file in as400
SQL SELECT after member MBR2 override

In the same way, we perform SQL SELECT after member override, we can execute SQL INSERT, SQL DELETE, and SQL UPDATE query as well which will change MBR2 of file WEB_3. Also at last we need to delete the override by calling stored procedure QCMDEXC from STRSQL as follows.CALL QSYS.QCMDEXC('DLTOVR FILE(WEB_3) LVL(*JOB)',0000000028.00000)

Using SQL Alias

We can use SQL CREATE ALIAS statement is used to create an alias(synonym) for each member of a multimember physical file to access each member through the alias object.

Alias is created once on a file member and the most important thing is there is no need for the member to exist at the time of the CREATE ALIAS on that member.

We can create an SQL alias from the STRSQL session using the below SQL statement.

CREATE ALIAS EASYCLASS1.ALIAS1ONMBR2 FOR EASYCLASS1.WEB_3(MBR2) 
CREATE ALIAS, Alias,SQL ALIAS in as400, SQL ALIAS in ibmi, SQL alias in db2
CREATE ALIAS

The alias ALIAS1ONMBR2 was created successfully in library EASYCLASS1. Since the alias name is a long name so the system itself defines its short name and creates a DDMF file type object in the library EASYCLASS1.

CREATE ALIAS, SQL create alias, alias, alias in db2, alias in IBMi, as400
SQL ALIAS
CREATE ALIAS EASYCLASS1.ALIAS1ONMBR2 FOR EASYCLASS1.WEB_3(MBR2) 

Here EASYCLASS1 is the library where you create the alias on a file member and ALIAS1ONMBR2 is the name of the alias you create. (In this example the name used in LONG, you can keep it short up to 10 characters as per your requirement).

CREATE ALIAS EASYCLASS1.ALIAS1ONMBR2 FOR EASYCLASS1.WEB_3(MBR2) 

Here EASYCLASS1 is the library where the file object exists and WEB_3 is the file name(multimember physical file) and MBR2 is the member on which we will be creating the alias. Basically, it represents member MBR2 of file WEB_3 in library EASYCLASS1.

You can now select the member MBR2 data using SQL ALIAS created on that file member. You can either use the short name of SQL alias or the long name of SQL alias in the SQL SELECT statement.

SELECT * FROM EASYCLASS1.ALIAS1ONMBR2
SELECT * FROM EASYCLASS1.ALIAS00001
SQL SELECT ALIAS, ALIAS, SQL ALIAS, DB2,AS400,IBMi
SQL SELECT ALIAS

In the same way, we perform SQL SELECT after creating SQL ALIAS on the file member MBR2, we can execute SQL INSERT, SQL DELETE, and SQL UPDATE query as well on the ALIAS created on that member i.e. ALIAS1ONMBR2 or ALIAS00001 to insert, delete and update record in second member MBR2 of file WEB_3 instead of its first member.

Related Post

Post a Comment

© AS400 and SQL Tricks. All rights reserved. Developed by Jago Desain