How to run SQL Statements in CL?

by using RUNSQL (refer - Runsql)

PGM

RUNSQL SQL(‘DROP TABLE QTEMP/TEMP/TABLE) COMMIT(*NONE) - DROP WILL DELETE THE

EXSISTING TABLE

MONMSG MSGID(SQL9010)

RUNSQL SQL(‘CREATE TABLE QTEM/TABLE AS(SELECT FLD1,FLD2,FLD3 FROM TESTPF) WITH DATA’)

COMMIT(*NONE) – THIS WILL CRAETE A NEW TABLE WITH FIELDS FLD1,FLD2,FLD3 FROM
TESTPF WITH THE RECORDS FROM TESTPF

Access path in AS400 and how to create the Access path ?

Access path describes the order in which records are to be read from the file

There are two types of access path Arrival and keyed sequence access path

It depends upon the declaration of key fields in DDS and if there is a key field then it is keyed access path
or it is Arrival access path

How to get date and time in CL?

Need to use rtvsysval command to reterive date and time in CL.

DCL VAR(&DATE) TYPE(*CHAR) LEN(8)

DCL VAR(&TIME) TYPE(*CHAR) LEN(6)

RTVSYSVAL SYSVAL(QDATE) RTNVAR(&DATE)

RTVSYSVAL SYSVAL(QTIME) RTNVAR(&TIME)

File A has 1025 records and how to retrieve the no of records in CL? –

By using RTVMBRD we can retrieve the no of records for a file in CL (refer to Local server program -
retrieve)

100 DCL &LIB TYPE(*CHAR) LEN(10) 07/30/20

200 DCL &MBR TYPE(*CHAR) LEN(10) 07/30/20

300 DCL &SYS TYPE(*CHAR) LEN(4) 07/30/20

400 DCL &MTYPE TYPE(*CHAR) LEN(5) 07/30/20

500 DCL &CRTDATE TYPE(*CHAR) LEN(13) 07/30/20

600 DCL &CHGDATE TYPE(*CHAR) LEN(13) 07/30/20

700 DCL &TEXT TYPE(*CHAR) LEN(50) 07/30/20

800 DCL &NBRRCD TYPE(*DEC) LEN(10 0) 07/30/20

900 DCL &SIZE TYPE(*DEC) LEN(10 0) 07/30/20

1100 07/30/20

1200 RTVMBRD FILE(MOHANLIB/TESTPF) MBR(TESTPF *SAME) + 08/24/20

1300 RTNSYSTEM(&SYS) RTNLIB(&LIB) RTNMBR(&MBR) + 08/24/20

1400 FILEATR(&MTYPE) CRTDATE(&CRTDATE) + 08/24/20

1500 TEXT(&TEXT) NBRCURRCD(&NBRRCD) + 08/24/20

1600 DTASPCSIZ(&SIZE) 08/24/20

1700 SNDPGMMSG MSG(&MBR)

Can we use procedure or subroutine in Cl and syntax for Subroutine in CL?

We can call subroutine by using CALLSUBR, SUBR, ENDSUBR (Refer -

[Link]
PGM
02
03 CALLSUBR SUBR(WHATEVER)
04
05 /*========================================*/
06 SUBR SUBR(WHATEVER)
07 RTNSUBR
08 ENDSUBR
09 /*========================================*/
10 ENDPGM

The RTNSUBR can be used like the LEAVESR in RPGLE, to exit the subroutine immediately. It
has an optional parameter, RTNVAL, that can be used to return a "return code". When I have
used this command it was to leave the subroutine, without a need for the RTNVAL. For
example:
How to handle error mesasage in CL and default monmsg types?

By using MONMSG command. There two types of MONMSG Generic level monmsg and Command level
Monmsg

Generic:
MONMSG MSGID (CPF0000)

Command level:

MONMSG MSGID(CPF0864)

List of messages:

MONMSG MSGID (CPF9821 CPF9822 …… CPF9830)

Conditional operators and condition in CL and syntax for conditions in CL? having a variable A and it has
to execute when the number is leasser than 10?

DCL VAR(&INT) TYPE(*INT) LEN(2)

:
DOFOR VAR(&INT) FROM(1) TO(10)
: (group of CL commands)
ENDDO

We can also use the IF command and check whether the A =10 and if the condition is satisfied then put
GOTO statement and go to end.

Diff b/w If then and If then Do –

If is normal condition statement

If the condition passes it will execute the THEN part in CL and do is normal command used in Then part.
(Refer -IFTHEN)

How to update the dataareaa –

By using command CHGDTARA command.

How to delete the records form CL

We can call an RPG program by supplying RRN number and delete the record or we can use a SQL
statement DELETE to delete the Records from a file or we can also use CLRPFM command. (refer -
delete)
100 PGM 07/29/20

200 RUNSQL SQL('Drop Table qtemp/temp_table') + 07/29/20

300 COMMIT(*NONE) 07/29/20

400 MONMSG MSGID(SQL9010) 07/29/20

500 RUNSQL SQL('delete from mohanlib/studpf WHERE + 08/03/20

600 STUDNO =3') COMMIT(*NONE) 08/03/20

1100 ENDPGM

Use of Ovrdbf and scenarios when to use Ovrdbf - Refer to PDF study material

OVRDBF is a command to temporarily overrides the parameters of a Data base file. It is used in
association with OPNQRYF, to use different file with same record format in a program.

It overrides the PF temporarily for a job.

You can use the Override with Database file (OVRDBF) command to replace the database file named in a
CL program or to change certain parameters of the existing database file.

By using OVRDBF we can share the access path to other programs. We can also set the pointer.

TO FIND A PARTICULAR STRING IN SOURCE PF –

FNDSTRPDM or option 25

How to compare two sources -

By using command CMPPFM or opt 54.

How to create multimember PF and ways to do that

ADDPFM – By using this command we can multiple members to a PF

We can use OVRDBF to select which member needs to be used in the CLLE

We can use EXTMBR keyword in F spec in RPGLE

MSGW in as400

WRKACTJOB – Then select F7 and type MSGW and *STS

WRKJOB <<JOBNAME>>

WRKMSGQ(QSYSOPR)

WRKUSRJOB

How to find the dependent logical files of a PF –

By using DSPDBR

Batch debugging?

(1) SBMJOB CMD(CALL PGM(AMINEM/ADDCL)) HOLD(*YES)

Job 300129/A07OPER/ABATCHD submitted to job queue ABATCHD in library

ASLMS000.

(2) STRSRVJOB JOB(300129/A07OPER/ABATCHD)

(3) STRDBG PGM(AMINEM/ADDCL) UPDPROD(*YES) OPMSRC(*YES)

(4) SOURCE APPEARS , PRESS F12 TO COME OUT TO COMMAND LINE

(5) WRKJOB JOB(300129/A07OPER/ABATCHD) ==> OPTION 43 TO RELEASE THE JOB

(6) AFTER RELAEASING THE SERVICE JOB WILL START AND WILL SHOW YOU THE SCREEN
AS BELOW:

(6) PRESS F10 AND ON THE COMMNAD LINE GIVE COMMAND 'DSPMODSRC'

(7) DSPMODSRC TO DISPLAY THE SOURCE AND TO PUT THE BREAK POINT

(8) F3 AND F12 TO GET THE SCREEN OF STEP(6), PRESS " ENTER TO CONTINUE"

(9) DEBUGGING WILL START AND PROGRAM WILL EXCUTE.

(10)ENDDBG

(11)ENDSRVJOB

Printer file? - refer program STUDRPGLE in local server

Difference between Static and dynamic sql?

Static sql means, the sql statement remains the same value what we gave the program and dynamic sql
means the values will be changed inside the program based the on the parameters we receive inside the
program

parameters with a? mark in the sql statement where we can substitute the value to the sql statement.
(example - DYNAMICSQL)

1 Hoption(*nodebugio:*srcstmt:*nounref) 05/28/20

100 FEMP_SQL IF E K DISK 05/28/20

200 FOPEN IF A E DISK 05/28/20

300 dEdept s like(EMPDEPT) 05/28/20

400 DMY_DS1 E DS EXTNAME(EMP_SQL) 05/28/20

500 D Prefix(P_) 05/28/20

501 /free 05/28/20

502 dcl-s query varchar(100); 05/28/20

503 dcl-s aaa char(5); 05/28/20

504 DCL-PI *n; 05/28/20

505 Parm1 Char(50); 05/28/20

506 end-pi; 05/28/20

507 EXEC SQL 05/28/20

508 SET OPTION COMMIT = *NONE; 05/28/20

802 QUERY ='delete FROM OPEN'; 05/28/20

803 Query += ' WHERE EMPDEPT1 = ?'; 05/28/20

805 aaa = Parm1; 05/28/20

806 05/28/20

807 EXEC SQL PREPARE PURGEPROCESS 05/28/20

808 from :Query; 05/28/20

809 //Execute the sql 05/28/20

810 exec Sql execute Purgeprocess 05/28/20

811 using :aaa; 05/28/20

812 *inlr=*on; 05/28/20

900 /END-FREE

Lifecycle of a cursor - The various steps are,

1. Declaration, 2. open, 3. Fetch, 4. close. ( example - cursor)

1 H OPTION(*NODEBUGIO:*SRCSTMT:*NOUNREF) 05/22/20

100 FEMP_SQL IF E DISK 05/22/20

101 FOPEN IF A E DISK 05/26/20

102 dEdept s like(EMPDEPT) 05/26/20

103 DMY_DS1 E DS EXTNAME(EMP_SQL) 05/26/20

104 D Prefix(P_) 05/26/20

200 /FREE 05/22/20

201 Edept ='JAVA'; 05/26/20

202 EXEC SQL 05/22/20

203 SET Option Commit = *none; 05/22/20

204 Exec sql 05/26/20

205 Delete from Open; 05/26/20

206 exec sql 05/22/20

207 Declare C1 CURSOR FOR 05/26/20

208 select * FROM EMP_SQL 05/26/20

209 where Empsalary between 60000 and 90000; 05/26/20

210 EXEC SQL 05/22/20

211 Open C1; 05/26/20

212 EXEC SQL 05/26/20

213 Fetch from C1 into :My_ds1; 01/13/21

214 Dow SQLCODE = 0; 05/26/20

215 /// dsply My_ds1; /// 05/26/20

216 Empid1 = P_EMPID; 05/26/20

217 Empname1 = P_EMPNAME; 05/26/20

218 Empsalary1 = P_EMPSALARY; 05/26/20

219 Empdept1 = P_EMPDEPT; 05/26/20

220 write Rec1; 05/26/20

221 EXEC SQL 05/26/20

222 Fetch next from C1 into :My_ds1; 01/13/21

223 //Fetch from C1 into :My_ds1; 01/13/21

224 Enddo; 05/26/20

226 *inlr = *on; 05/22/20

300 /END-FREE

Can we retrieve data in bulk using cursor without using loop and it should fetch 10 records at a single
shot

Below is the code for SQLRPGLE,

What are Sql set options - Sql set option establishes the processing options used in SQL statements, it
will not be executed and we can use only one SET option in SQL.

exec sql SET OPTION COMMIT = *NONE,

NAMING = *SQL,

CLOSQLCSR = *ENDMOD,

DATFMT = *USA,

TIMFMT = *USA ;

In rpg or rpgle i have a PF and its associated LF and pf doesnt have Keys and how to diff between that PF
and LF? how to differentiate the fileds of the PF and LF?it will read the values

From the file which has been read by the program even if they the same fields.

We should rename the LF record format name so the program will read the LF. Or it will throw error like
both files are having same record format name.

Can we read and write to the same file in one program?

Yes we can do that by adding A to add records and Option I in F spec.

How to write the program name while writing the records to a file?

By using PSDS we can write the program name to the file.

Dcl-Ds PgmStatus PSDS;

JOB Char(10) Pos(244);

USER Char(10) Pos(254);

Program Char(10) Pos(334);

End-Ds;

I’m calling a program and its failing is there a possibility to get the line number?

By using PSDS we can get the line number.

By using H option(*srcstmt) we can get the line number or

By using code 0202 we can get the line number. (Refer - PSDS)

External datastructure? refer to Go4AS400

File information data structure and in which situations we will use it?

BY using fsds we can get the rrn number and we can the file name, rec name, and the record in the file

we can check the exsistence of records in a file by using FSDS without reading the file. Refer FILEDS

100 Hoption(*Nodebugio:*Srcstmt) 07/24/20

200 FFILEDS_PF IF E K DISK 11/10/20

300 F INFDS(FILEDS) 07/24/20

400 * 07/24/20

500 DFILEDS DS 07/24/20

600 DRECORD *RECORD 07/24/20

700 DMEMBER 129 138 07/24/20

800 DDB_RRN 397 400I 0 07/24/20

900 * 07/24/20

1000 * 07/24/20

1100 DDATA1 DS 07/24/20

1200 DFLD1 10 07/24/20

1300 DFILLER1 2 INZ('--') 07/24/20

1400 DFLD2 10 07/24/20

1500 DFILLER2 2 INZ('--') 07/24/20

1600 DFLD3 4 0 07/24/20

1700 DFILLER3 2 INZ('--') 07/24/20

1800 * 07/24/20

1900 * 07/24/20

2000 DDATA2 DS 07/24/20

2100 DFLD4 6 01/19/21

2200 DFILLER4 2 INZ('--') 07/24/20

2300 DFLD5 10 01/19/21

2400 DFILLER5 2 INZ('--') 01/19/21

2500 DFLD6 5 0 07/24/20

2600 DFILLER6 2 INZ('--') 07/24/20

2700 DFLD7 10 0 07/24/20

2800 * 07/24/20

2900 * 07/24/20

3000 * 07/24/20

3100 C READ FileDS_pf 07/24/20

3200 C DOW NOT %EOF(FileDS_pf) 07/24/20

3300 C EVAL FLD1=MEMBER 07/24/20

3400 C EVAL FLD2=RECORD 07/24/20

3500 C EVAL FLD3=DB_RRN 07/24/20

3600 C EVAL FLD4=CSNBR 07/24/20

3700 C EVAL FLD5=CSNAME 07/24/20

3800 C EVAL FLD6=CS#OPN 07/24/20

3900 C EVAL FLD7=CS$OPN 07/24/20

4000 C DATA1 DSPLY 07/24/20

4100 C DATA2 DSPLY 07/24/20

4200 C READ FileDS_pf 07/24/20

4300 C ENDDO 07/24/20

4400 C SETON LR

How differentiate between normal data structure and File information data structure –

The FSDS will be declared in the F spec with the file with key word INFDS and

Normal data structure will not be declared as INFDS with file, that way we can differentiate between
them.

Hoption(*Nodebugio:*Srcstmt)

FFileDS_pf IF E DISK EXTMBR('*ALL')

F INFDS(FILEDS)

DFILEDS DS

DRECORD *RECORD

DMEMBER 129 138

DDB_RRN 397 400I 0

DDATA1 DS

DFLD1 10

DFILLER1 2 INZ('--')

DFLD2 10

DFILLER2 2 INZ('--')

DFLD3 4 0

how to use dtaaarea in program? by using dataarea data structure with keyword dtaara and by using in
to read date dorm Dataarea and out to write the records to Dataarea.

To use data area - C *DTAARA Define Dft_Div A 4

or we can use DS with datatara keyword or by usinf data area datastructure - (Refer - Dataarea)

1 DDads1 DS Dtaara('Pnrno') 01/10/21

2 DFld1 1 10 01/10/21

100 c *Lock In Dads1 01/10/21

200 C Fld1 Dsply 01/10/21

300 C Unlock Dads1 01/10/21

400 C Seton LR

Compiler directives?

Diff between dountil and dow?

Dounitl will be executed at least once even if the condition is failed.

Dowhile will be executed only if the condition passes.

DOU(a=1) and how to i come out of the loop?

if A value is 2 and we are using Dou a=1 means it will go insisde the loop below the loop

we can put a if codition if a <> 1 means then leave the loop. Refer Dou

100 hoption(*nodebugio:*srcstmt) 08/10/20

200 dA s 1P 0 Inz(2) 08/10/20

300 /free 08/10/20

301 Dou (a=1); 08/10/20

302 If A<>1; 08/10/20

303 leave; 08/10/20

304 endif; 08/10/20

305 dsply 1; 08/10/20

306 enddo; 08/10/20

307 *inlr = *on; 08/10/20

400 /end-free

For numeric field what i will use? diff between Zoned and integer and packed?

Packed will assign only one hexadecimal value at the end F but zoned will add F for all the numbers.

Refer Num_field.
NAME ATTRIBUTES VALUE

PACKED6 PACKED(6,0) 123456. '0123456F'X

PACKED6A PACKED(6,0) -000123. '0000123D'X

ZONED6 ZONED(6,0) 123456. 'F1F2F3F4F5F6'X

ZONED6A ZONED(6,0) -000123. 'F0F0F0F1F2D3'X

Different types of data types in D spec?

A- This is Character type

B- This Binary type
D- This is Date field

F- This is floating point

G- This is graphic data

I- This is signed integer field. We can have negative and positive values in signed
integer field.

N- This is an indicator field and can be used with indicator data structure.

P- This packed field. The data in the packed field will be as ‘00000F’X. For example, if
there are two numbers 123 and -123 with length of 6. Then the packed field will be
‘000123F’X and ‘000123D’X, where F is for positive field and D is for negative field.

S- This is Zoned field. The data in the packed field will be as’F0F0F0F0F0F’X. For
example, if there are two numbers 123 and -123 with length of 6. Then the packed field
will be ‘F0F0F0F1F2F3’X and ‘F0F0F0F1F2D3’X, where F is for positive field and D is for
negative field.

T- This is an time field.

U- This is unsigned integer field. We can only positive values in unsigned integer field
starting from range 0.

Z- This is an timestamp field

* = This is an pointer.

How to check the existence of record in a file before writing the record to file?

We can used FSDS to check the existence of a record in file before writing to it.
- in Cl we can use rtvmbrd file(mylibrary/myfile) nbrcurrcd(&records)

- We can FSDS to check the existence of record or we can use Setll and %equal to find if the record is
there in the file (Refer Equal) or we can do chain and %found to find

the record in the file before writing it to the file.

100 Hoption(*Nodebugio:*srcstmt) 08/10/20

200 FEQ IF E K Disk 08/11/20

201 DEmpname2 S like(Empname1) 01/15/21

300 /Free 08/10/20

301 Empid1=1; 01/15/21

302 Setgt *hival Eq; 01/15/21

303 Readp rec1; 01/15/21

304 Empname2 = Empname1; 01/15/21

305 if %equal(); 08/11/20

306 dsply 'found'; 08/11/20

307 else; 08/11/20

308 dsply 'not found'; 08/11/20

309 endif; 08/11/20

310 *inlr=*on; 10/03/20

400 /end-free

Suppose I have two records and how to read the records?How to read the first record and what if I have
two ID as 1 same value and what to do in that situation?

We can use Chain

so it will fetch the first record alone. (refer - Chain)

Activation group

Activation group is releated to a job, when job ends activaton group [Link] giving the DFtact grp as
*N0 and giving the name to the activation group, it will reduce time for program execution and the
activation group created will not be killed until the user sign off the [Link] will be in No status and we
can use wrkjob and opt 18 to see the activation group when running a job or program. If it is a ILE
program then we need to give H Dftactgrp (*No) for subprocedurs when its is not compiled as a Module.
Move opcode and Can we move the character value to a numeric field using move opcode? or how to
covert the character value to numeric and move them in RPG fixed format?

We can use Move opcode to move character values to numeric fields and numeric values to charater
field. It will move the values from right to left and will not check for the

Data type of the fields. (refer - Move)

100 Hoption(*Nodebugio:*srcstmt) 08/11/20

200 dA s 5P 0 08/11/20

300 db s 5A 08/11/20

400 c Move 'abc' A 01/05/21

401 c* Eval A ='abc' 01/14/21

402 c Move 563 B 09/21/20

403 c a dsply 09/21/20

404 c b dsply 09/21/20

500 c seton lr

How to find that how many duplicates are there for each ID in a file using sql?

By using Group by we can get the original values and by using Having along with count we can

get the number of duplicates in a file.

SELECT EMPID ,count(*) FROM Eq GROUP BY EMPID HAVING count(*) >1

When passing 4 parameters from program A where program B has only 3 parameters? what will
happen? –

The receiving pgm will have the first three values from the called program A.

That means the first values will be passed to B program without any error.

When using subfile when we enter option 4 in wrkmbrpdm one of the prompt appears and when
coming to the same screen that option remains same? why is it happening? -Refere AS400 study
material.
How to read a record with field name starting with A alone in Rpg using chain?

How to use likewise operator in sqlrpgle?

using host varialbe in sqlrpgle with trim (refer google)

The Host variable name is :Variable name

C Eval HstNameFix = '%MyText%'

C/Exec SQL

C+ Declare CsrC0 Cursor for

C+ Select Name1F from Adresse

C+ Where Name1F Like Trim(:HstNameFix)

C/End-Exec

Syntax for join in sql

select emp_sql.EMPNAME,EMP_SQL.EMPSALARY,EMP_SQL.EMPDEPT,

[Link],[Link],[Link] FROM EMP_SQL INNER JOIN EQ

ON EMP_SQL.EMPID=[Link]

select emp_sql.EMPNAME,EMP_SQL.EMPSALARY,EMP_SQL.EMPDEPT,

[Link],[Link],[Link] FROM EMP_SQL LEFT JOIN EQ

ON EMP_SQL.EMPID=[Link]

SELECT statement run complete.

select emp_sql.EMPNAME,EMP_SQL.EMPSALARY,EMP_SQL.EMPDEPT,

[Link],[Link],[Link] FROM EMP_SQL RIGHT JOIN EQ

ON EMP_SQL.EMPID=[Link]
SELECT statement run complete.

select emp_sql.EMPNAME,EMP_SQL.EMPSALARY,EMP_SQL.EMPDEPT,

[Link],[Link],[Link] FROM EMP_SQL FULL JOIN EQ

ON EMP_SQL.EMPID=[Link]

SELECT statement run complete.

(refer W3schools)

Supoose if I pass 1 parametr to a program having 2 param how to over come that error?

There will be an error pointer not set for location referenced and we need pas the correct parameter to
overcome that error or we can check the joblog. Error name - Pointer or parameter error - (Pointer or
parameter error) (Refer - PARAMETERS)

how to find the file identififers in RPG

DSPFD to see the file identifier and need to find in RPG.

In subfile how to give prompt option for a particular field in Subfile?

We can use RTNCSRLOC to get the field name and SFLCSRRN to get the RRN number. By putting a
condition like if the field is ID then we can display the prompt screen. But we can’t use RTNCSRLOC in
subfile it can used in display file only.

How to use display file in CL?

[Link]

how to display blank records in subfile

We need to check the condition if RRN =0 then turn off the SFLDSP indicator (refer - Loadall)

Ex - ///////////To display empty subfile//////

if rrn = 0;
 *in46=*off;

endif;

/////////////////////

pointer or location referenced error

clrsfl in subfile - we should turn SFLCLR indicator then write to sflctl and turn off the SFLCLR indicatior
(refer - Loadall)

EX - endsr;

/////////Clear SFL////////////

begsr clr;

*in45=*on;

write CTL;

*in45=*off;

endsr;

///////////////////////////

can we move ABC to numeric field in rpg

When we try to move ABC to numeric field, it will move its number to numeric field for

Ex: ABC means 123 to numeric field

refer - Move program

Hoption(*Nodebugio:*srcstmt)

dA s 5P 0

db s 5A

c Move 'xyze' A
c Move 123 B

c a dsply

c b dsply

c seton lr

How to identify duplicate records in a file using sql

Group by is used to give the values in file without duplicates or we can also use DISTINCT to get the
records without duplicates

how to fetch duplicate records in sqlrpgle –

BY using Group and following query we can identify it

SELECT EMPID,count(*) FROM sql_dup GROUP BY empid HAVING count(*) > 1

- It will give the duplicate values alone and count(*) will give the number of values in file

In cl how to set the pointer to cl program to a file –

POSBDF or OVRDBF

how to trap errors in rpg

By using MONMSG and refer MONMSG in this same document which is already explained

how many files can we read by using open id concept in cl

5 Files is allowed with Opind concept

how to delete a single record which is having duplicate in sql

BY using ROW_NUMBER() keyword in SQL or we can also try with TOP keyword

SELECT Row_Number() over (Order by flatfile),flatfile FROM

mbalamuru/flatfile
[Link]
server-table/#:~:text=So%20to%20delete%20the%20duplicate,and%20after%20the%20delete
%20occurs.

REF keyword in PF?

It is used refer the attributes from the other file. We need to use the keyword REF(<<Filename>>)
before the record format name. In field, we need to use the keyword REFFLD(<<FIELDNAME>>)
Field level keywords?

Level check error?

When the file is compiled with LVLCHK parameter as *YES, then we need to complie the programs
dependent on this file or LVLCHK error will occur. If its value is *NO, then the record format level
identifier is not checked, hence no error.

Joint logical files?

Program status data structure?

Program status data structure is used to get the program information like status code, crt date, crt time,
user name, program name. BY using this we can get the error line number.

File status data structure

*PSSR
It is an error status subroutine, which will be executed if the program gets into error.

Check

It is used check the non-occurrence of an character.

For example, if I am searching M in Mohanraj, then it will return the position 2, because at staring
position M is present.

Scan

It is used check the occurrence of an character.

For example, if I am searching M in Mohanraj, then it will return the position 1, because at staring
position M is present.

SFLRCDNBR
In subfile, if we press enter it will come back to first page. To avoid this we are using this keyword to
keep track of records.

sflrcdnbr = recno

except

This keyword is used to write the output to printer files or Output files

%error

These are error handling techniques.

diff bet eval and move

Eval will check for data type, if we are moving numeric values to charater field it will be error, eval will
be moved from left to right.

For example, if I am moving Mohan to an variable A,

Then it will start moving from M. If we need to move from right to left we can use EvalR

Move opcode will not check data type and move the values from right to left.

For example, if I am moving Mohan to an variable A,

Then it will start moving from N. If we need to move from left to right we can use MoveL
to find value in a array?

We can use Lookup to find a value in array,

arr(1) = 'Cornwall';
arr(2) = 'Kingston';
arr(3) = 'London';
arr(4) = 'Paris';
arr(5) = 'Scarborough';
arr(6) = 'York';

n = %LOOKUP('Paris':arr)= In free format RPGLE

It will give the position as n = 4

C ‘Kingston’ lookup arr2 81 = In fixed format RPGLE

If the element is found then the indicator 81 will be *on or it will be *off.

In above the *IN81 will be *on.

pgm and endpgm are mandatory keywords

No, when passing parameters we need to give PGM and ENDPGM. But it is good to use these keywords
always in the program.

IF setonlr and return is not declared and what error will occur when didnt mention it

The Compiler cannot determine how the program can end.

decimal data error

When passing characters fields to numeric in parameters this error will occur

To avoid these errors, we can pass it as hexadecimal value. For example if field B is 10 length

Then pass it as 01234567890F'X. Filed B value will be 1234567890

if we pass 4 to program which is having 5 paramters what will happen

Referenced to parameters is not passed or Pointer not set for location referenced.

If we are having program with duplicates in file how to write original values alone

By using Group by in sql

how to read a file in cl with loop –

Dowhile, in that we need to specify the condition

Or we can read the file by below command,

READ: RCVF,

MOMMSG(CPF0864) THEN GOTO (END)

Read records

Goto READ.

It will read until it reached EOF.

count of file

Count(*), we can also count for separate fields

test opcode

Is used to check whether any character is used in Character field.( Refer - Testn)

how to convert date from iso to usa format –

a=%date(b:usa) or by doing substring or CVTDATE.

how to find whther an array is empty or not –

sorta and put a condition wheter array(1) =*blanks and if it is blanks then it is an empty array.

D Alpha S 1 DIM(10) DESCEND

D Number S 1 0 DIM(10)

* For alpha, sort descending and check first element or

* sort ascending and check the last element

C SORTA Alpha

C IF Alpha(1) = *BLANKS
* Empty

C ELSE

* Not empty

C ENDIF

* For numeric, use BIF %XFOOT and check for 0

C IF %XFOOT(Number) = *ZEROS

* Empty

C ELSE

* Not empty

C ENDIF

C RETURN

totDigits = %XFoot(%len(qty))

H spec - Hoption see it in google

how to acess file from one server to another without using ftp? - ddm

Steps to create DDM file -

CRTDDMF FILE(TEST/INCOPY) RMTFILE(SPIFFY/INVENT)

RMTLOCNAME(KC105)

CPYF FROMFILE(TEST/INCOPY) TOFILE(TEST/INVENTDDM)

MBROPT(*ADD)

Refer - [Link]
how to create multi member PF and add records in multi member PF?

First cretae a PF and in wrkmbrpdm type the file name

Then use command ADDPFM and create the Mbr - MBR2 and MBR3

Use UPDDTA command to add date by mention the member name with it.

Refer - Multi

how to read third member of a file from cl and rpg? -

In Cl we can use OVRDBF by mentionong the member name

OVRDBF FILE(MULTI1) TOFILE(MULTI) MBR(MBR3)

Refer - Multimbrcl

In Rpg - We can use Extmbr('*all') with F spec

Refer - Multimbrpg

If there is thre departments it,hr,as400 for a field department and how to read all the three in a single
shot? - read setgt dow not eof(file)

We can use settl *loval and dow not %eof and read all the records.

compile time and arrys?

data structures?

What Are The Different Types Of Messages In Cl?

Flat file? how to read the seprate fields in flat file? - Flatfile is a PF type file and it will not have any
source. The field will be same as the file name and we can inert the

records in that file as whole single one. We can use flat file to move data to alcl variable like notes data.
By using DSPFD we can see the file descrption and using sql we can find

the filed names of the flat file. (Refer - Flatfile)

H spec dump - H Debug(*no) means no dump will be performed and H Debug(*Yes) means dump
operation will be performed.

H spec Nodebugio - If it is not used seperate break points will be set for the fields in a file. If used it will
set a single break point.

Differnt types of cursor in sqlrpgle

Suppose Im having two files with same field name how to distinguish between them? - By using Prefix
(Refer - Dynamic sql)

Subprocedures and modules - Refer - Subprocedures and module document. Pgm - Getday - 2 and
Getdayname - 1.

IF there is a file with data,

EMPID EMPSALARY

101 - 2000

101 - 3000

102 - 5000

102 - 3000
105 - 5000

how to get the total sum of all IDS like 101 = 5000, 102 = 8000, 105 = 5000?

SELECT Sum(EMPSALARY),Empid FROM mohanlib/Emp_sql GROUP

BY Empid

How to find whther there any character in a Mobile phone no? - Testn

When the Indicator associated with Testn is on thn there is only numerics in the field or it is an error
field.

RTVJOBA

In kfld what if I didnt pass the value for 3rd field - It will execute without picking the 3rd field value
maybe the 3rd field value will be 0. (Refer - Kfld)

*arrays in synon? If I create array in pgm a and after closing the pgm a, what if i tried to open it in pgm
c? will it open?

What if there is job running so much of time? Reading each record in a file one by one? what can we do
in that time?

Can we add records in PMTRCD? (Refer - TJLIPVR In Eagle Dev)

12/01:

How to handle errors in Cl? Monmsg, Progam level and cmd level messsage and refer google
Can we read a PF file in CL? We can read a PF file with DCLF command and we can read only one file and
we need to use OpenId concept to read till 5files

How to we know EOF has been reached in CL? CPF0864

One practical scenario where Opnqryf is used? (Refer - Readfile1)

how to find how many records are present in a PF through CL? RTVMBRD (refer - Reterive)

Have u used RTVJOBA? Which user is using the job? Rtvjoba command is used to give job name,no,user
name, currentuser of the job, Sbmuser,outq,jobq,Jobtype,Sbmjobnbr,jobsts.

Suppose I need to get the data from datarea from Cl? RTVDTAARA

What cmd to is used in CL to convert the date format? CVTDAT command is used to convert date. (Refer
- CVTDATE)

In cl, give some example of OVRDBF usage? (Refer - Readfile1)

How to find the keys are present in a Logical file? Dspfd

How to copy the data from CSV file to PF file and vice versa? CPYTOIMPF and CPYFRMIMPF

What is %Scan and %Check in rpgle? %scan - to find occurence of charater and %check to find non
occurence of character (Refer - CHECK_SCAN)

%Diff in rpgle? How it will calculate the differnce between the dates? %diff is used to return differnce
bettween two dates or two times (Refer - As400sce4)

How can we populate the current date in rpgle? %Date()

How to find the date before six months from today's date? We can use %Month or %Days and Subdur
and need to find them? (Refer - As400sce4)

C DATE1 SUBDUR 02:*D DATE2

C EXTRCT DATE1:*D DATE5

Have u used Monitor block? Monitor block is used for error handling method along with On error
opcode.

For example if trying to convert the Blanks to zero's with %Dec it will throw an error so there we can
code the logic in on-error block according to the situation.

*PSSR? Give an example of program error? (Refer - PSDS)

Have u used Chain(n)? To read the record without applying the lock

I have a PF? I want to read all the records in RPG? There is no key field in the file? How to read all the
records? If there is no key field then we can use *start

If there is key file we can use *Loval

Setll *Start PF

Read pf

Dow not %Eof(pf)

field operations

Read PF

enddo

Diff between Setll *start and *loval? When there is a key field we can use *loval and when there is no
key field we can use *start.

How to do them in Sqlrpgle? Declare Cursor, Open cursor, Fetch from C1 into file name or datastructure
name,Fech next from C1(Refer - Cursor)
How to read from last record in sqlrpgle? Fetch Last from C1 ,Dow sqlcod = 0, Fetch Prior from C1.

What will happen if I didnt spcify Fetch Next from instead of that if i give fetch from? No differnce
between them.

Dow Sqlcod = 100? It is Eof in Sqlrpgle.

Have u worked with %Substring and example of that %substring?

Have u used TEST(D)? It is used to test the date value

Result

C *ISO TEST(DE) Alphannumeric field

Make one pattern

**

***

****

How to print the same pattern in Rpgle? (Refer - AS400SCE)

(Try it in For loop)

String 1 ='Mohanraj'

I need to find how many 'a' are used? - (Refer - AS400SCE1)

String 3 = %xlate(locase:Upcase:String1)

Pos = %scan('a':String)

Dow Pos<>0

Num = Num +1

Pos = ('a':string1:Pos+1)

Endddo;

Num dsply

Sql querries Locate, digit, accept?

PF - 1

Student name Dept marks

Mohan It 80

Shomi ECE 70

Ram It 50

Vicky ECE 90

how to get the count of for each dept with total student no?

SELECT dept,count(studname) FROM studmarks GROUP BY dept

Need to find who the highest marks?

SELECT Max(marks) FROM studmarks

SELECT EMPNAME,EMPSALARY FROM emp_sql WHERE EMPSALARY < (Select

Max( EMPSALARY ) from emp_sql )

Transaction file:

Cust id Transaction date

101 02/02/2020(charater format)

102 01/01/2021

Pendig file

Cust id

Need to write rpgle program to find the customers who has done the transaction 6 months before from
todays date and we need to write that cust no to pending file? (Refer - AS400SCE4)

[Link] would you achieve this requirement with out using RPG/RPGLE pgm,but by using only CL?Read a
database file and display file contents on the screen when enter key pressed the next record should be
displayed on [Link] the last record is reached or when F3 key is pressed the program should exit
if the file is empty,a message should be displayed indicate that there are no records to display. Database
file Name=EMPDBF Fields in EMPDBF to be displayed on screen Employee Number- EMPNUM(5,0)
Employee Name- EMPNAM(30,A) Employee Address-EMP ADDR(50) ?

We need to use two display file one to get the data and one to display them.

Refer - [Link] and

[Link]
How many MONMSG commands can declare in a CL program? - 100 command level for a single
command and 100 program level

where will control be passed after the execution of the *pssr subroutine if the factor2 of the endsr?
Control will return to the next sequential instruction

what is message subfile?..Message subfile is special file contains multiple messages taken from program
message queue and placed in message sub file for display on the screen.(need to check)

I have 1 rd in my flat file.

say reord from 1 to 10 position : 'AS400NDB2400'

I need to change the value from N to Y

using SQL stmt ...How can I update this....?

UPDATE TT SET TT = SUBSTR(TT,1,6) || 'Y' || substr(tt,8,6)

if &var='good++++' &var2='day' &var *cat &var2 &var1 *tcat &var2 what is output

Answer:

Var1 *cat Var2 = 'Good day' (Included trailing spaces in

first variable)

Var1 *tcat Var2 = Goodday (Removed Trailing spaces from

first field).

How do you read a subfile record which is in output mode?

Chain with SFLCSRRN

can we create a member in a logical file? You can add members to a LF. All the members in a PF must be
added to its logicals using ADDLFM

i want to display the 10000 record in a subfile by using loadall , can we do it? No we cant because the
buffer size is 9999.

How to read 2 positions at a time in a data area? I have

created a data area of length and i have to read the values

of the data area like 91-92,93-94 at a time up till 100? To read the specified length of a data raea in cl
there we can specify the substring length in RTVDTAARA command.

PGM

0002.00 DCL VAR(&AB) TYPE(*CHAR) LEN(2)

0002.01 DCL VAR(&I) TYPE(*DEC) LEN(2) VALUE(1)

0003.00 READ: IF COND(&I *LE 24) THEN(DO)

0004.00 RTVDTAARA DTAARA(DURGA4001/TESTDTA (&I 2)) RTNVAR(&AB)

0004.01 CHGVAR VAR(&I) VALUE(&I +2)

0004.02 IF COND(&AB *NE ' ') THEN(DO)

0004.03 SNDUSRMSG MSG(&AB)

0004.04 ENDDO

0004.05 ELSE CMD(GOTO CMDLBL(END))

0006.00 GOTO READ

0007.00 ENDDO

0008.00 END:

0009.00 ENDPGM
write the SQL statement to retrieve the 2nd Highest salary ammount(File Name="EMPLOY",Fi..

SELECT SALARY FROM EMPLOYEE WHERE SALARY < (SELECT MAX

(SALARY) FROM EMPLOYEE) ORDER BY SALARY DESC FETCH FIRST 1

ROW ONLY (need to check)

14/01:

How to design a program like if i give a file name it should disply all the library names where it is stored?

Which subroutine is initialed called when runing rpgle? what will happen when *inzsr it is not used?
from where the program will start?

*inzsr will be called defaultely and when it is not used it will start from beginning of the program.

Joint logical files? whther it can be used in sql? Yes we can run Joint logical files in sql. Example -
FINDPRO file in XGSI.

What are the indicators used in Read operation ? HI LO EQ and their line no? Hi - 74 lo - 76 Eq - 78.

What is subsystem? What is the parent subsytem of i-series? if i submit a job which subsytem will be
taken directly?

Subsytem is a specialized environment used for the execution for the jobs. Qbatch is the default sbs.

Interactive jobs can be submitted?

Default subsystem in as400? Qbatch

Decimal data error in a job? we can find through debugging the batch job and job is in hold, then how to
do it? we can use batch debugging to find them.

it is also normal process like as batch job steps...

when we found job in MSGW using WRKSBMJOB,simply we can enter into job log using 5 option.

after we start at command line.

step1-> STRSRVJOB jobnum/jobname/user

step2-> STRDBG pgmname

when we press enter,the control will directly goto

the error occurred line,here we can change any values directly using EVAL then after press F3,it will sho
wjob log screen,then again press f3 to come to MSGW [Link] press 7(display message screen) to
continue the execution of batch job we press G(continue).then it will execute remaing steps without
abnormal ending the job.

Level check error? how to resolve it without using Chgpf? In Cl we can use OVRDBF with LVLCHK as *NO
and in RPG there is no other way.

Scan and check opcodes? to check occurrence and non occurence of character.

Sort data in an array? we can use Sorta.

C Sorta Arrayname,

Look for any data in array? If I didnt get any record in array what value will i get in that array? If we use
an indicatior with Lookup it will turn it off when the record is not there

when the record is present the indicator will be on. EQ - In (Refer - Array1)

Accepath and dynslt? Refer study material.

Setll and setgt?

what will happen if i give *loval setgt and readp? *loval setgt - it will read from 2nd record if we use
[Link] using readp it will not read any record.

If we give *hival setgt with read it will not read any reord and if we use readp it will read the reords.
(Refer - Equal1)

Covert float value 99.9999 and covert to decimal? Num = %Dec(99.9999:5:4) (NEED TO CHECK)

Diff between dspf and subfile? main compomemts for subfile and diff bettween sflrcd and sftctl?Sflpag
and sflsiz? Refer google.

Can we display 110000 records in a subfile? No it wont be allowed because of buffer size.

%Xlate? Used to convert from Lower case to upper case (Refer - AS400SCE1)

How to call the programs in free format and how can we pass the parameters?

**free

02 ctl-opt option(*nodebugio:*srcstmt) ;

03 dcl-pr OtherPgm1 extpgm ;

04 Parm1 char(5) ;

05 Parm2 packed(3) ;

06 Parm3 char(1) ;

07 end-pr ;

08 dcl-s Var1 char(5) ;

09 dcl-s Var2 packed(3) ;

10 dcl-s Var3 char(1) ;

11 Var1 = 'Hello' ;
12 Var2 = 1 ;

13 Var3 = '' ;

14 OtherPgm1(Var1:Var2:Var3) ;

15 if (Var3 = 'Y') ;

Chain operation in free format?

how to grant the object authority other than GRTOBJAUT command?

User defined command?

other than chgdtara and out there is any command to update dataarea?

declare three files in cl? Open id concept

How to pass parameters in cl if we are not specifying pgm and end pgm commands? To pass parameters
PARM is a neccessary keyword and we need to specify the parameter name in PGM keyword. In Param
we should specify that variable name to passed and we need to declare those variables in a cl) or we can
concat those variable into a single variable and use

it in PGM.

External ds and normal ds? In E DS we can use ecternal files and fileds and DS is a normal DS.

Move flat string value to ds? Yes we can move a Flat string to a DS.

error handling in rpg? Monitor block or PSDS.

max number of member inb file? *nomax in Chgpf

CHGPF FILE(EQ) MAXMBRS(*NOMAX)

RGZPFM? what is diff bettween CLRPFM and sql delete operation?

After reorganizing, the records in ACCOUNT get arranged as per the key value of ACCOUNT.

Clrpfm will compress the deleted record space and Delete sql wil not compress the space.

Delte will not clear the space and clrpfm will clear the space

Sql joint querry? how to code in sqlrpgle? By using cursor

Journals?

how to use commitment control in RPG and cl?

PGM

STRCMTCTL LCKLVL(*CHG)

CALL PGM(*LIBL/CCTEST2)

COMMIT

CALL PGM(*LIBL/CCTEST2)

ROLLBACK

ENDCMTCTL

RETURN

ENDPGM

FCCTESTPF UF A E K DISK COMMIT

DCOUNTER S 5I 0
C DO 7 COUNTER

C EVAL CCFLD1 = COUNTER

C EVAL CCFLD2 = 'TESTING CC'

C WRITE FCCTESTPF

C ENDDO

C EVAL *INLR = *ON

C RETURN

What is the purpose of PR and PI in calling program and called program? in Called program PR is comp..

In Prototype we define the variables as parameters for calling. In Procedure interface, we define both
the variables and the calculation or function of the procedure. Prototype is mandatory in both calling
and called programs because we have to pass parameters and receive some parameters while calling

wat is the difference between dataarea and data queue?

A DataQ is erased as soon as the data is retrieved from it.

Whereas the data area can have the data stored in it until

the user opts to clear it. In DataQ one had variable for

arrival date,time, the sender id, and the key(in case of

keyed dataq)- this is an advanced feature in DataQ

How to improve the perofmane of a PF in as400?

If the file is in production and we need to acces the file in Dev where the file will not exsist and how to
do it?

We can use DDM concept for this. So we can access the file in prod from Dev.