Total Time: 1 hr Tot. Marks: 50 Tick one or more correct choice/s (1 Mark each) 1. Which of the following is not a valid CL datatype?
(a) *dec (b) *char (c) *log (d) *lgl Ans-(c)
2. Predict which programs will be executed in the following code when &Opt = ‘1’: IF COND(&Opt *EQ ‘1’) THEN ( Call Pgm4) ELSE CMD(Call Pgm3) CALL Pgm(Pgm2) CALL Pgm(Pgm1) Pgm4 , pgm2, pgm1
3. Which of the following files can be used in the TOFILE parameter of CPYF? a) Logical file
b) Join Logical file c) Tape file
d) None of the above (c)
4. Which of the following commands cannot be used on the command line? a) RTVDTAARA
b) CRTDTAARA c) CHGDTAARA d) All the above
(A)
5. What is the command used to find all logical files based on one physical file? a) DSPDBR
b) DSPOBJD c) DSPPFM d) a and b (a)
6. Which is the command used to find the access path of a file? a) DSPFFD
b) DSPFD c) DSPOBJD d) All the above (b)
a) Printer file, Database file b) Display subfile, Message subfile c) Message subfile, Database file d) Database file, Display subfile c)
Two types of files are supported in CL procedures and programs: display files and database files.
You can send a display to a workstation and receive input from the workstation for use in the procedure or program, or you can read data from a database file for use in the procedure or program. Subfiles (except for message subfiles) are not supported by CL procedures and programs.
Up to five display or database files can be referred to in a CL procedure.
The following statements apply only to database files used with CL procedures and programs:
– Only database files with a single record format may be used by a CL procedure or program.
– The files may be either physical or logical files, and a logical file may be defined over multiple physical file members.
– Only input operations, with the RCVF command, are allowed. The WAIT and DEV parameters on the Receive File (RCVF) command are not allowed for database files. In addition, the SNDF, SNDRCVF, and ENDRCV commands are not allowed for database files.
– DDS is not required to create a physical file which is referred to in a CL procedure or program. If DDS is not used to create a physical file, the file has a record format with the same name as the file, and there is one field in the record format with the same name as the file, and with the same length as the record length of the file (RCDLEN parameter of the CRTPF command).
– The file need not have a member when it is created for the module or program. It must, however, have a member when the file is processed by the program.
– The file is opened for input only when the first Receive File (RCVF) command is processed. The file must exist and have a member at that time.
– The file remains open until the procedure or OPM program returns or when the end of file is reached. When end of file is reached, message CPF0864 is sent to the CL procedure or program, and additional operations are not allowed for the file. The procedure or program should monitor for this message and take appropriate action when end of file is reached.
The following statements apply only to display files used with CL procedures and programs: – Display files may have up to 99 record formats.
– All data manipulation commands (SNDF, SNDRCVF, RCVF, ENDRCV and WAIT) are allowed for display files.
– The display file must be defined with the DDS.
– The display file is opened for both input and output when the first SNDF, SNDRCVF, or RCVF command is processed. The file remains open until the procedure or OPM program returns.
Note: The open does not occur for both types of files until the first send or receive occurs. Because of this, the file to be used can be created during the procedure or program and an override can be performed before the first send or receive. However, the file must exist before the module or program is compiled. Override with Display File (OVRDSPF) Allows a run-time override of a file used by a procedure or program with a display file. Override with Database File (OVRDBF) Allows a run-time override of a file used by a procedure or program with a database file.
Indicator variables are declared in your CL procedure or program in the form of logical variables with names &IN01 through &IN99 for each indicator that appears in the device file record formats referred to on the Declare File (DCLF) command.
When you use CL support, the file referred to is implicitly opened when you do your first send, receive, or send/receive
control. An opened database file is closed when end of file is reached, or when the procedure or OPM program in which it was opened returns or transfers control. Once a database file has been closed, it cannot be opened again during the same call of the procedure or OPM program.
Do not specify LVL(*CALLER) on the Reclaim Resources (RCLRSC) command in CL procedures and programs using files. If you specified LVL(*CALLER), all files opened by the procedure or OPM program would be immediately closed, and any attempt to access the file would end abnormally.
You use the open file identifier (OPNID) parameter to uniquely identify an instance of a declared file so that multiple files can be declared. If you use the OPNID parameter, then for a field, the CL variable name is the field name preceded by an ampersand (&), the OPNID value, and an underscore (_). For an option indicator, the CL variable name is the indicator preceded by an ampersand (&), the OPNID value, an underscore, and “IN”.
You cannot use record formats that contain floating point data in a CL procedure or program. Except for message subfiles, any attempt to send or receive subfile records causes run-time errors.
8. Max no. of files that can be declared in a CL program (a) 0
(b) 1 (c) 50 (d) 9999 (b)
9. How many files can be updated through CL (a) 0
(b) 1 (c) 20
(a) We can’t update a database file through a CL program 10. What is the function of DSPPGMREF?
a) Displays list of all files referred in the program b) Displays the list of all objects used in the program c) Displays all copy books used in the program
d) Displays list of all call programs used in the program
(b)
Use Short Answers: (2 Marks each)
1. What is the difference between program level and command level MONMSG? 2. What is the difference between TFRCTL command and CALL command? 3. How can the data be shared across different jobs?
4. Differentiate between data base files and data areas
5. How do you compile RPG program having compile time override? 6. What is wrong in the following program:
PGM
MONMSG CPF0000 DCLF FILE (INPUT) ENDPGM
Program level MONMSG should be specified only after all DCL statements 7. How do you know the keys used in a physical file without using SEU? 8. How to extract a word “hello” from the string “hello world”?
9. Which has the highest priority if you give both program level MONMSG and command level MONMSG? 10. How to use multiple files in CLP?
Mention True/False:(1 Mark each)
1. Two objects with same name and type cannot exist in different libraries FALSE 2. The built-in menu called MAJOR organizes all the AS/400 commands TRUE 3. SNDF writes a record to a display or a data base file. FALSE
4. The value of a CL variable can be changed using CHGVAR. TRUE
5. The current library cannot be a duplicate of any library in the library list FALSE
6. The RETURN command in a program removes that program from the program stack TRUE 7. A data area is an object used to hold data for access by any job running on the system. TRUE 8. Library list is an object TRUE
9. LDA can be used to pass information to a subprogram without the use of a parameter list TRUE
10. SNDPGMMSG command allows us to send messages to a work station user or the system operator. FALSE
Answer the following questions: (2 marks each)
1. Not all CL commands can be used in a CL program (TRUE/FALSE) TRUE
2. Declare commands must precede all other commands except the PGM command. (TRUE/FALSE) TRUE 3. ENDPGM command is a mandatory command in a CL program. (TRUE/FALSE) FALSE
4. ___MONMSG_____ Monitors for escape, status, and notify messages sent to a program's message queue 6. __________________ identifies a command in a CL program and is used for branching purposes
1. In the same library there can be objects of the same name of different object types. This statement is
a) True b) False
c) None of the above a)
2. Types of jobs on OS/400 are a) 2
c) 4 d) 5
Job types
The iSeries server processes several different job types. You can select one of the following job types to learn more about that job type.
Server jobs are jobs that have set the server type using the Change Job (QWTCHGJB) API, and they will have an additional classification of Server with one of the following job types:
Autostart
An autostart job is started automatically when the subsystem it is associated with starts.
Batch
A batch job is a predefined group of processing actions that is submitted to the system.
Communications
A communications job is a batch job that was started by a program start request from a remote system.
Interactive
An interactive job requires input from a signed-on user and an iSeries server.
Prestart
A prestart job is a batch job that starts before a work request is received. The two types of prestart jobs:
• Prestart communications - The job is a communications batch job that starts running before a remote system sends a program start request.
• Prestart batch - The job is a batch job that starts before a work request is received. Reader and writer
A reader job is a spooled input job, and a writer job is a spooled output job.
Subsystem
The subsystem job provides control over an active subsystem.
System
System jobs are created by the operating system to control system resources and perform system functions.
3. Save file is an object and it is used for the backup. This statement is a) True
b) False
c) None of the above a)
4. In join logical file you can have multiple files and multiple record formats. This statement is
a) True b) False
c) None of the above b)
5. To show the physical file member, its attribute and its logical files commands used is a) DSPDBR
c) DSPFD d) DSPPFM a) and d)
6. To look at the job correct ser of command used is
a) WRKACTJON, WRKSBMJOB, DSPJOB, WRKUSRJOB b) WRKSBMJON, WRKSBSJOB, WRKUSRJOB
c) WRKACTJOB, WRKSBSJOB, DSPJOB, WRKUSRJOB Ans : - c)
7. DYNSLT keyword is used in a) Physical file
b) Logical file c) Both
d) None of the above b)
8. Command function and command attention Key performs the same function and have same concept. . This statement is
a) True b) False
c) None of the above b)
9. OPNQRY FILE AND LOGICAL FILE both are used to access the database file only. This statement is
a) True b) False
c) None of the above a)
10. Which statement is false
a) QUERY400, OPNQRYF, LOGICAL FILE all are objects. b) Logical file is an object
c) QUERY400 is a system supplied utility d) OPNQRY is not an object
a)
11. There are 2 physical files. File(a) is having 3 logical file and other File(b) is having 2 logical file. Which can be easily complied with out deleting its dependent logical file a) File(a)
b) File(b)
Ans : - c)
12. In join Logical File Min. files required are a) 1
b) 6 c) 32
d) None of the above Ans : - a)
13. In join logical file Max files required are a) 2
b) 4 c) 6 d) 32 Ans : - d)
14. To find a string CL command used is a) FNDSTRPDM
b) ALCOBJ c) DSPFD
d) None of the above Ans : - a)
15. Please read the following CL statements Program A
CALL PGM (B) OPEN FILE X OPEN FILE A Program B
OVRDBF FILE (X) FILE (Y)
OVRDBF FILE (A) TOFILE (B) OVERSCOPE (*JOB) RETURN
The file opened in the program A is a) File A,X
c) File X,B d) File Y,A c)
16. Please read the following CL statements. Program A
OVRDBF FILE(B) TOFILE(C) OVRDBF FILE(A) TOFILE(B) OPEN FILEA.
File opened will be a) A.
b) B c) C
d) None of the above c)
16. Read the following statements Program A
OVRDKTF FILE(A) ToFILE(B) ENDPGM
Program B OPEN FILEA File Opened will be a) A.
b) B
c) None of the above A)
17. You can send a message in the program through subfile. . This statement is a) True
b) False
c) None of the above Ans : True
18. QCMDEXC is a system supplied API. This statement is a) True
b) False
c) None of the above Ans : - True
19. You can display a error message only on 24th Line in a display file. . This statement is
a) True b) False
c) None of the above Ans : - b)
20. You can define (a) Only one file in CL program .(b) & update the database file through CL program .In this statement
a) A is true B is false b) B is true A is false c) A & B are true d) A & B are false Ans:- a)
21. You can get the user information in the CL program by using command a) RTVJOBA
b) RTVUSRPRF c) DSPJOBA d) RTVSYSVAL e) None of the above b)
22. (A ) call in the CL program will pass control to the next program. (B) while RETURN command remove the procedure from the call stack .In This statement a) A is true B is false
b) B is true A is false c) A & B are true d) A & B are false b)
23. (A) Call in the CL program will pass control to the next program and again back to the calling program.(B) while TFRCTL command removes the calling program from the call stack. In this statement
a) A is true B is false b) B is true A is false c) A & B are true d) A & B are false Ans : - c
a) True b) False
c) None of the above Ans : - a)
QUESTIONS : simple ####################
1. Give examples of DCL, DDL, DML SQL commands. A. DATA CONTROL LANGUAGE(DCL)- grant , revoke. DATA DEFINITION LANGUAGE(DDL)- create, alter , drop.
DATA MANIPULATION LANGUAGE(DML)- select, insert, update, delete. 2.What are data types? What is meant by null? Give examples of operators.
A. Data types are attributes of columns, literals and host variables. They are SMALLINT, INTEGER, FLOAT, DECIMAL, CHAR, VARCHAR, DATE, TIME, TIMESTAMP, GRAPHIC, VARGRAPHIC. NULL is a special value that indicates the absence of data in a column. Operators: - , + , * , / , :: concatenation, date, day, year, days, substr , current date, current time, user.
3. What are the column or aggregate functions available in SQL?
A. AVG for average, COUNT for counting the number of rows in the result table, MAX which returns the maximum value in the column , MIN which returns the minimum value, SUM which gives the sum total of the column.
4. Explain the usage of the terms GROUP BY, HAVING, ORDER BY.
A. GROUP BY - the column function will calculate its result based on the individual groups created by GROUP BY specification creating one result for each group. HAVING - is used to specify the conditions each row of each returned group must satisfy.
ORDER BY - used to sort the output of a query using the column names or position of column in the list of columns named in the select expression.
5. What does the LIKE and IN and BETWEEN search operators allow you to do? A. They search for rows where the column specified satisfies one among a number of values.
LIKE - to select rows based on a similarity of partial strings.
IN - to select rows where the column values are one among a specific list of values. BETWEEN - to select rows having column values within the given limits.
6. What are the DB2 objects that can be created using CREATE statement? What are the DB2 objects you require before creating a Table?
A. TABLE, INDEX, VIEW, SYNONYM, ALIAS, STOGROUP, DATABASE, TABLESPACE.
Tablespace and Database are required before creating a table.
QUESTIONS : medium difficulty ##########################
1. What is a CURSOR and how is it operated for read, update, delete?
A. A cursor is a named control structure used to make a selected set of rows available to a program one row at a time for read or update.
DECLARE CURSOR, OPEN CURSOR, FETCH CURSOR, CLOSE CURSOR
are used to operate the cursor. The results table will be created during the execution of the
OPEN CURSOR statement. The values are fetched from the table row into the host variables and the cursor position remains on the row until the next FETCH or CURSOR CLOSE statement. During this time ,the cursor position could be used to update or delete this row from the table using the WHERE CURRENT OF clause.
2. What is the difference between Dynamic SQL and Embedded SQL?
A. Dynamic SQL statements are prepared and executed within a program while a program is being executed. The SQL source is contained in the host variables rather than being hard coded into the program and may change from execution to execution.
Embedded SQL statements are hard coded within the application program and are prepared during the program preparation process before the program is executed.
3. What is the difference between cascade and restrict with ref. to DELETE statement? What is a primary key and a foreign key?
A. Cascade and Restrict are part of the delete rule when specifying the referential constraints between two tables. Cascade on Delete - deletes all dependent rows from the dependent table while deleting a row in the parent table. The Restrict rule fails the delete request if a dependent row exists.
A primary key is the unique identifier of the rows in a table.
A foreign key is a column or combination of columns in a table whose values are required to match with those of the primary key in some other table.
4. What is the difference between a base table, view, synonym, alias, index?
Base table - table that physically exists and has physical stored records ,can be updated.