DB2

RELATIONAL DATABASE – DB2/400 1. What are members? Members are separate entities & they are components of As/400 object. 2. What is a Physical file? • Physical file nothing but a table contains an only one record format in which we can describe the field definitions and descriptions. • A description of how data is to be presented to or retrieved from a program and how data is actually stored in the database 3. What is a Logical file? It’s a logical view of physical file. No data’s stored here. 4. What is Join Logical file? Join logical file joins more than one file. The maximum number of files allowed to join is 32 (thirty two). Update is not possible through JLF. 5. What is a Non-join logical file? A Non-Join Logical file can either be a simple logical file, which contains only one record format of multiple format logical files, which contains more than one record format. In non-join logical file updating is possible and we can add 32 record format. 6. What is Multiple Members? A file on the AS/400 may be “sub-set” in order to maintain separate groupings of records with their own access path. The file description is common to all of the members. 7. What is field reference file? A physical file that contains no data, only descriptions of fields. It is used as a reference for the field descriptions of other files. All the files needed for an application can be defined in a field reference file to enforce naming conversation, and to reduce programmer’s workload. 8. What is a Keyed physical file? Defining a field as a key in physical file. Hence we can access records through the key field. 9. What is a composite key? Defining more than one key as a key field in physical file. 10. What is arrival sequence? The records will be accessed the way in which records are added to pf. 11. What is relational database? A database that is organized and accessed according to relationships between data items Frequently Asked Questions in DB2/400 Page 1 of 16

12. What is the maximum number of key fields allowed defining in a physical file? 120 13. Distinguish between physical file and logical file? Physical file - contains only one record format. - Record will be loss. When we compile. - We can update - We can use REF keyword. - We can’t use select/omit level - Contains actual data. Logical file

-

contains more than one record format. No record loss when we compile. We can’t update. We can use REFACCPTH, DYNSLT. Select/Omit level used in Non-join logical file. Doesn’t contain data but it provides view from the physical

file. 14. What is DDM? Distribution Data Management is a function of the OS/400 that allows an application program or user on one system to use database files stored on remote system. 15. What is the purpose of Level Check parameter in a Physical file? Specifies whether the level identifiers of the record formats in the physical file are checked when the file is opened by the program. 16. What is the purpose of DYNSLT keyword? It is a file level keyword used in a logical file. If you specify this in a file level, the system doesn't perform record selection until the program reads file. Then on the Select/Omit criteria, it selects the records from the specified file. 17. What is the difference between access path and Dynamic select? Dynamic select occurs whenever the program reads file. But access path occurs before the file is read (but not necessarily). Because access path maintenance performed on the file. 18. What is stands for FIFO, FCFO, and LIFO? Define All? i) LIFO (Last in First Out) -----> Records with duplicate key values is retrieved in Last in First Out order by the Physical sequence of the records. ii) FIFO (First in First Out) -----> Records with duplicate key values is retrieved in First in First out order by the Physical sequence of the records. iii) FCFO (First in First Out) -----> records with duplicate key values is retrieved in First Change First Out order by the Physical sequence of the records. Frequently Asked Questions in DB2/400 Page 2 of 16

19. What is the difference between regular logical files and join logical file? • Regular logical file contains more than one record format. • Record format name in regular logical file should be same based on the physical file. • Join logical file contains only one record format. • Record format name in join logical file should be different. • Through logical file we can make changes in physical file. • Through join logical file we can't make changes in physical file. 20. What is the necessary keyword used in a physical file to refer field definitions from the field reference file? REF, which is a file level keyword, is necessary to define at file level in a physical file. The syntax is as follows: REF (library name/name of the field reference file) 21. What is the purpose of reorganizing the physical file i. e., RGZPFM? Even if you delete the records in a physical file through the program, still the space used by the deleted records not used by the other purpose. Hence using RGZPFM command we can compress the deleted records space. 22. What is the purpose of CHGPF (Change physical file) command? CHGPF command is used to change the attributes of a physical file. Attributes like • Maximum number of members • Member size • Level check enforced • Open data path is shared 23. What are the different types of access paths maintained on the file? Immediate: All the access paths (open and close) associated with a file will be updated whenever changes made to a file. Rebuild : Only open access paths will update whenever changes made to the file. Rebuild takes place whenever remaining access paths will be open. Delay : Rebuild will not take place. 24. What is the difference between externally described file and program described file? • The field definitions and descriptions are defined out side of the program is called an externally described file. • The field definitions and descriptions are defined in side the program is called a program described file. Frequently Asked Questions in DB2/400 Page 3 of 16

25. What are the necessary keywords for the Join Logical file? JFILE, JOIN, JFLD, JREF. 26. What is the necessary keyword for Non-join logical file? PFILE (which is record level keyword) 27. How many levels are there in a Physical file? Four levels: They are File level; Record level, and Field level, Key field level. 28. What is the use of UNIQUE keyword and what level it is defined? It will avoid entering duplicate key values. We have to define it in a file level. 29. At what level S, O is defined and what they will do? S, O are to defined at key field level. The purpose Select/Omit logic is depends on the criteria (condition) given at the key field level (At function entry) it selects and omits the records. The S/O is allowed in Logical file only. 30. What is the difference between packed decimal and zoned decimal? Packed decimal: One digit occupies 1 byte. Zoned decimal: One digit occupies 2 bytes. 31. What is default data type (if you define decimals '0') in Physical file? Packed decimal 32. What is default data type for the fields (sub fields) defined in data structures in RPG? Zoned decimal 33. What is the use of JDFTVAL in join logical file? If you specify JDFTVAL at file level, even the primary file record does not match with secondary file record, the join takes place. 34. Is it possible to create a physical file without DDS? Yes. With the help of CRTPF we can achieve this. But we have to give record length. 35. How to change file attributes such as size, file wait time, record wait time etc., permanently? Using CHGPF command 36. What are the different definition levels in Data Description Specifications? File level, Record level, Field level, and Join level, Key field level, and Select/Omit level. 37. What is the default value for the number of increment for the physical file? Three Frequently Asked Questions in DB2/400 Page 4 of 16

38. When does the DFT keyword in PF be used? To specify the default value it a field. 39. What is Multi-format logical file? Logical, which uses fields from two or more physical files. 40. Can fields be concatenated in a logical file level? Yes. By using CONCAT keyword 41. When would the ALL keyword be used? Use with Select or Omit, to select/omit records. 42. What are the different types of keywords in display files? File level; Record format level, Field level 43. What is Multi-format logical file? Logical, which uses fields from two or more physical files. 44. Is it possible to join the same file to itself? Yes. 45. How can see logical file based on which physical file and vice versa? DSPDBR What is the command to view all dependent file of PF? DSPDBR 46. How can u check no of records in a file? DSPFD 47. What is Qualified & Unqualified path? Qualified path means mentioning object name along with library name while unqualified path means directly writing the object name. 48. Which path a good programmer should use? Unqualified path. 49. How many types of files are available on As/400? There are 2 types of files on As/400 they are Databases files & Device files. 50. What is the command to know key fields of file? DSPFD (F4) *ACCPTH (Access Path). 51. How is data stored in PF’s? In fixed length records. 52. How many triggers can be associated with a single PF? 6 (max) Frequently Asked Questions in DB2/400 Page 5 of 16

53. How can we know the triggers associated with the PF? DSPFD with parameter TYPE (*TRG) 54. How can we add & remove trigger to a PF? ADDPFTRG & RMVPFTRG 55. F is the type of file designation Used for externally described files given when file types are? U&I 56. What is a table?

A table is collection of data elements in one column; data elements must be of same type and same length.

57. The LF is associated with how many PF’s? At least one PF at a time. 58. A Multiple format Logical File is also known as? Union File 59. How Union file does maintain Record formats? 1 Record format for each PF 60. DB2 is what type of Database? It is an Integrated RDBMS. 61. An access path may be in which sequence? It can be in Arrival & Keyed sequence 62. Records can be Read/Update/Deleted with which file? Simple Logical File & Physical File. 63. Which type of object is used in DB2/400? *FILE 64. What is SQL? SQL is an interface for programming language. 65. A Query can be displayed without saving it? True 66. Which are the Query selection criteria, which can be given in a Query? LIST 67. A Query can be run in which mode? Frequently Asked Questions in DB2/400 Page 6 of 16

Batch & Interactive 68. What does DFU program you to do on a record? Insert, Update, Delete & File Enquiry. 69. Why the Declare cursor statement is used for? To define & name the cursor & specify rows to be fetched. 70. What do we can do with the Embedded SQL statements? We can Insert/Update/Delete records, fetch records; fetch values from records into variables. 71. What are the values SQLCOD when there is an error in fetching the records specified in the select statement? -Ve value 72. A file cannot have more than 1 unique key? False 73. Which are the DB2 tools to protect Integrity of the database? Journaling & Commitment Control, Referential Integrity Embedded SQL & Object/Record Locks. 74. A program variable coded in an Embedded SQL statement is referred to as? Host Variable 75. Which is the better option to write a transaction (order header and detail transaction) using two physical files or one join logical file (on those two physical files) in a program and why? Using two physical files is the better option because records can't be written through join logical file. 76. Can we have a multi record format join and non-join logical file based on one physical file? We can only have Join logical file based on one physical file. 77. What is difference between Journaling and commitment control? Journaling record all transactions and is used for restoring or reverting back the transactions onto the database file for a particular time span. Commitment control ensures that a transaction is updated successfully onto the data base (all relevant files). 78. What is difference between Multi record format Join and Non- Join logical file?

Frequently Asked Questions in DB2/400 Page 7 of 16

Multi record format logical file contain more than one record format (based on one or more physical file) with join logical file only read operation can be done whereas in non-join logical file all operations can be done. 79. Can a file be journaled without using it under commitment control? Yes 80. Can a file be used under commitment control without being journaled? No 81. Why in AS/400 database is called as integrated database? In AS/400 the database is integrated into the operating system and LIC of the AS/400. So it is called as integrated database 82. What is file description? A file description is information that describes the characteristics of the associated file 83. What are the types of logical file? Non-join logical file

Join logical file

84. What are the level keywords in PF? FILE LEVEL keywords RECORD LEVEL keywords FIELD LEVEL keywords KEY FIELD LEVEL keywords 85. What are the level keywords in non-join LF/ File level, record level, field level, key field level, and select/omit level 86. What are the level keywords in Join LF? File level, record level, join level, field level, key field level, and select/omit level 87. What is the command to add a member in PF? ADDPFMBR 88. What is difference between SRCPF and PF?  A source physical file contains the source for the various objects created.  A physical file contains data and the record format and the record format can be different for different PF. 89. What is dynamic select? Selection and omission of LF records performed during process, instead of access path is maintained 90. How many files can be joined at a time? Frequently Asked Questions in DB2/400 Page 8 of 16

Two files 91. What is the maximum number of files can be joined? The maximum number of files is 32.Only one primary file and the remaining are secondary files. 92. What is CPYF? CPYF is used to copy records from one file to another file 93. What is REF? REF is a file level keyword. This keyword is used to specify the name of the file from which the field descriptions are retrieved 94. What are the data types in DB2? A-Character P-Packed decimal S-Zoned F-Floating point H-Hexa decimal L-Date T-Time Z-Timestamp 95. How we can consider the signs of the values (-ve and +ve) for a field? By using key field level keyword SIGNED 96. How do you invoke SQL? By using STRSQL 97. How do you view the field description of a PF? By using command DSPFFD 98. What are the two rules in setting a constraint? Delete rule, Update rule 99. What is the command to remove PF member?

RMVM 100. What is the command to remove PF constraint? RMVPFCST 101. How do you insert records to a PF? By using DFU STRDFU-5 option to update 102. What are the keywords under delete rule?  Restrict  Cascade Frequently Asked Questions in DB2/400 Page 9 of 16

 Set null  Set DFT  No action

103. What are the keywords under update rule?  Restrict  No action 104. List out text definition commands? COLHDG, TEXT 105. Which command is used to remove all the data from the Physical File? CLRPFM 106. How many members can be created with in a single Physical File? 32,767 107. Which command is used to extract the particular string from the member? FNDSSTRPDM. 108. How to rename the Physical File member? RNMM. 109. What is the object type of field reference file? *FILE. 110. What is access path? An access path describes the order in which records are to be retrieved from a file. 111. What are the database file maintenance commands? DSPFFD, DSPDBR, DSPPRMREF. 112. Which command is used to delete the physical file? DLTF. 113. What are the three types of access path maintenance methods? *IMMED, *REBLD, *DLY. 114. What is the default data type in a physical file? Packed decimal. 115. How to view the data from the database file? Object header, Record format, Access path, and data member. Frequently Asked Questions in DB2/400 Page 10 of 16

116. What is a maximum no. Of key fields in PF? 120. 117. ______ Command removes deleted records from one member of a physical file. RGZPFM. 118. Which command is used to add a physical file constraint? ADDPFCST. 119. What are the valid name types in PF? R and K. 120. What is the maximum number of fields in a PF? 8000 fields. 121. What is the use of CPYF?  To print all records in a PF.  To transfer records from on PF object to another. 122. What are the mandatory keywords for Join LF? JFILE, JOIN, JFLD, JREF. 123. What keyword is used to reference the same field in another file? REF 124. What is the use of DSPPFM? Used to display the records in the file. 125. What is shared access path? To share the open data path by various programs in a same job. 126. What are the keywords used in join logical file? JDFTVAL, JDUPSEQ, JFILE, JOIN, JFLD, JREF 127. What are key fields used in non-join logical file? PFILE, DYNSLT, REFACCPTH, RANGE, COMP, VALUE 128. Give five file level keywords UNIQUE, DYNSLT, FIFO, REF, LIFO 129. List out some Record level keywords FORMAT, PFILE, TEXT 130. What are Field Level Keywords?

Frequently Asked Questions in DB2/400 Page 11 of 16

ALIAS, ALWNULL, CCSID, CHECK, CKMSGID, CMP, COLHDG, COMP, DATFMT, DATSEP, DFT, EDTCDE, EDTWRD, REFFLD, EDTWRD, REFFLD, REFSHIFT, TEXT, TIMFMT, TIMSEP, VALUES, VARLEN. 131. Give some select/omit level keywords ALL, CMP, COMP, RANGE, VALUES 132. List out Text defition keywords COLHDG, TEXT 133. What is database file? A collection of data item stored in such a way as to minimize redundancy and allow for system future use 134. What are the relational operators? 1. Sequence, 2.select, 3.project, 4.union, 5.join 135. What are relational operators? A database that is organized and accessed according to relationship between data items 136. Give the components of physical file? Object header Record format Access path Data Member 137. List out database management commands

DSPFFD-display file field description DSPFD - display file description DSPDBR - display database relation DSPPGMREF - display program reference

138. What is Record Level Keyword? i. FORMAT ii. TEXT 139. I. II. III.

What is adv. of Field Reference File? It enforces the naming convention Reduces Programmer workload It defines in one place all data elements in the application for all users.

140. In DDS, comments are defined by putting * in column -----------7 141. ------------------------------- Keyword to specify that the access path

information for this logical file is to be copied from another PF or LF.

Frequently Asked Questions in DB2/400 Page 12 of 16

REFACCPTH 142. REFACCPTH keyword can't contain ------------, -------------- fields.

Key, Select/Omit

143. One JoinLF format combines fields from multiple PF formats (T/F) True 144. What is the purpose of Join Keyword? Join identifies which two files are joined by these specifications. 145. What is Dependent File? File containing foreign key in a refential Constraints. 146. -------------- Fields cannot be concatenated field Floating Point 147. What is the concept of multiple members? It provides a way to organize physically data in data file. 148. What is the use of keyword RANGE? To set records from a PF based on a min/max value of a field. 149. In which column you have to specify J in JoinLF? 17 150. What are the command alternatives to find string? DSPFFD, DSPDBR, DSPPGMREF. 151. How many members can be added in one physical file? 32,764. 152. ________ Command used to retrieve all the primary files, when it is

not matching with secondary file. JDFTVAL.

153. ________ Command is used to retrieve the duplicate fields during Join file. JDUPSEQ. 154. Define Access Path and define its types? An Access path describes the order in which records are to be retrieved from a file. The Records can be retrieved from a Physical file or Logical file either by Arrival Sequence or Keyed sequence. For Logical files you can also select records using select or omit key words. ARRIVAL SEQUENCE ACCESS PATH: Frequently Asked Questions in DB2/400 Page 13 of 16

It is based on the order in which the records arrive and store in the file. Records can be accessed. Sequentially, where each record is taken from the next sequential physical position in the file. Directly by relative record number, where the record number is identified by its position from the start of the file. KEYED SEQUENCE ACCESS PATH: It is based on the contents of the key fields as defined in DDs. This type of access path is updated whenever records are added or deleted or when records are updated in the content of key field is changed. 155. ______ Command is used to allow you to look at the data in the file in

character or hexadecimal format. DSPPFM (Display physical File Member command)

156. In which column we have specified ‘R’ (Record Level Key words) in DDS? 29th Column. 157. What is Storage Allocation?

ALLOCATE Parameter. This parameter controls the storage allocated for members when they are added to the file (CRTPF, CHGPF, CRTSRCPF, and CHGSRCPF commands). The storage allocated would be large enough to contain the initial record count for a member. If you do not allocate storage when the members are added, the system will automatically extend the storage allocation as needed. You can use the ALLOCATE parameter only if you specified a maximum size on the SIZE parameter. If SIZE (*NOMAX) is specified, then ALLOCATE (*YES) cannot be specified. 158. DB2 integrated into _______and ________ Operating system and Licensed Internal Code 159. What we can used to manipulate DB2? DFU, SQL/400, QUERY/400 160. Give the constraint management commands a) CHGPFCST - Change constraints b) DSPCPSTS - Check pending status c) RMVPFCST - Remove pending status 161. What are the disadvantages of logical file? Overhead of access path 162. _________Find out matched records and delete the records CASCADE 163. Which command is used to create a member? STRSEU Frequently Asked Questions in DB2/400 Page 14 of 16

164. Which function key used to delete particular record in DFU? F23 165. Which function key used to insert data in DFU? F9 166. What about TEXT keyword? It is used for programmer’s description. Not present at the user view. 167. What about COLHDG keyword? It is used for user’s description. Not present at the programmer view. 168. Give the SQL statements? Select, insert, update, delete 169. In PF what is the field length? 10 170. How will create journals? CRTJRNRCV, CRTJRN, STRJRNPF. 171. What about JDUPSEQ? It is used to sequentially arrange the duplicate records. 172. ______ Command removes deleted records from one member of a

physical file. RGZPFM. 173. ________Is used to sequence the records in descending or ascending order Instructor 174. What is parent key? Fields in physical file that are unique ascending and identified in a referential constraint it may be same as the primary or unique key 175. What is foreign key? Fields that must match (if not null) a parent key 176. What is dependent file? File containing foreign key in a referential constraint 177. What is parent rule? File containing parent key in a referential constraint 178. What is the use of constraints? To avoid the delete or update of the records Frequently Asked Questions in DB2/400 Page 15 of 16

179. What is the rule to set constraints? PF doesn’t contain lf. It needs unique key. 180. When are the Unique Constraints executed? During Insert. 181. When are the Referential Constraints executed? Insert, Update & Delete. 182. What is the difference between triggers and referential integrity? Triggers are programs (which carry out a particular business function) invoked either before or after manipulation of the records in a database. Referential integrity maintains the integrity of a database using key fields. 183. Define Referential Constraints? Relationship between records identified parent keys and records identified by foreign keys. 184. What is referential integrity? State of a database in which each non-null foreign key value must have a matching parent key value. 185. Benefits of Referential integrity a) b) c)

Independent of Application programs Continuous enforcement of referential constraints Easier application coding

186. What is Primary Key?

⇒

⇒ Field(s) in a PF that are unique, ascending and not ⇒ Null ⇒ Only one per file ⇒ May be used as a parent key in a referential constraint .

Frequently Asked Questions in DB2/400 Page 16 of 16