Ways to insert records in multiple members of a multimember physical file in AS400

Ways to insert records in multiple members of a multimember physical file in AS400
Ways to insert records in multiple members of a multimember physical file in AS400,OVRDBF and SQL INSERT, Override database file, multimember physical file in as400, ibmi
Ways to insert records in multiple members of a multimember physical file in AS400

Let's take a look at how to add data in different members of the multimember physical file

There are several ways to insert data in multiple members of the multimember physical file. We will discuss the possible ways here.

You can easily add records to the first member by only using INSERT because the default insert happens on the *FIRST member of any file.

But what about inserting records to the second or any other member? Any idea.

Using OVRDBF(Override Database File) command on the command line and SQL INSERT

Let's suppose that we have file WEB_3 in library EASYCLASS1 having 3 members namely WEB_3, MBR2, MBR3. Now let's add the record to the second member MBR2.

Steps to Add record in multiple members of a file

OVRDBF

Execute below the OVRDBF command from the command line to override the file to the member to which you want to add the records.

OVRDBF FILE(WEB_3)             
       TOFILE(EASYCLASS1/WEB_3)
       MBR(MBR2) OVRSCOPE(*CALLLVL)

OVRDBF command to override members,OVRDBF, OVRDBF in CL programming, ORDBF cl command, OVRDBF in AS400,ibmi
OVRDBF to override member

Just type command WRKJOB on the command line and take option 15(Display File overrides if Active) to see the overrides on the file WEB_3.

WRKJOB - File overrides option 15 in IBmi AS400
WRKJOB-File Overrides-option 15
Since you override the file member MBR2. So whatever activity you perform on file WEB_3 will happen on MBR2 only. So Let's Add a record in MBR2 of file WEB_3 using SQL INSERT

Using STRSQL - SQL INSERT

Type STSRQL command on the command line and pressing Enter will start the SQL session. Once the SQL session is open. Run the below SQL INERT statement

INSERT INTO EASYCLASS1/WEB_3 VALUES('TEST')

You will see the record gets inserted in file WEB_3 in library EASYCLASS1.

SQL INSERT, Insert records in multiple member of a multi member physical file using OVRDBF and SQL INSERT statement in A00,ibmiS
SQL INSERT

DLTOVR command to delete override: MUST

Warning!
Once the record is inserted in member MBR2 of the file WEB_3 in library EASYCLASS1, Just delete the file member override using command DLTOVR(Delete Override).DLTOVR FILE(WEB_3)

once you run the DLTOVR command, the specified override in file member will be removed.

WRKJOB- option 15 (File overrides), ovrdbf, wrkjob, ibmi, as400
WRKJOB - File Override

Using SQL to add records in multiple members of a physical file

You will find the blog here for Using SQL with Multimember physical file in AS400. Working with multimember physical files using SQL

Related Post

Post a Comment

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