Mainframe For Dummies

In DB2, the columns defined as NULL needs to be handled carefully else it will throw null exception error, in order to over come this error data type can be handled by using null indicator . NULL is stored using a special one-byte null indicator that is "attached" to every nullable column. If the column is set to NULL, then the indicator field is used to record this. Using NULL will never save space in a DB2 database design - in fact, it will always add an extra byte for every column that can be NULL. The byte is used whether or not the column is actually set to NULL. The indicator variable is transparent to an end user Consider below Table : Create Table SAMP_TAB SN CHAR (10) SNAME CHAR (10) STATUS CHAR (2) NOT NULL BY DEFAULT CITY CHAR (10) NOT NULL Note :: Unless you specify NOT NULL, the default is to allow for NULL In above table SN and SNAME columns holds null values by default, in order to handle these null variables we need to have NULL-INDICATORS declares in the Pro...

Question: HOW MUCH SPACE OS ALLOCATES WHEN YOU CREATE A PS OR PDS? Answer: 56 KB Question: MIN NO OF DATASET NAMES (PDS) IN ONE DIRECTORY BLOCK? Answer: FOUR - SIX Question: THE MAXIMUM NUMBER OF STEPS IN A JOB? Answer: 255 Question: MAX. NO OF DD STATEMENTS IN A JOB Answer: 3273 Question: What is the maximum number of instream procedures? Answer: 15 Question: for how long a job can be executed continuously in a mainframe Answer: 248 DAYS Question: How much is memory space involved, when we code BLOCKSIZE,TRK & CYL Answer: One block constitutes 32KB of formatted memory/ 42KB of Unformatted memory, 6 blocks makes one Track & 15 Tracks makes one cylinder. Question: What is DSNDB06 ? Answer: This is the Place where DB2 Catalog resides Question: What is the use of DSNDB07 ? Answer: This is the area where sorting takes place in DB2 Question: WHAT IS DATACOM DB? Answer: IT IS A DATABASE USED WITH VSE. Question: What are the maximum and minimum sizes of any CONTROL AREA (VSAM datas...

Relational Database Management System(RDBMS) Universal Database(UDB) This is one of the sub systems in Mainframes. Any number of Sub Systems can be created in Mainframes as per the requirements. Hierarchy of DB2 Sub system: TS – Table Space INS – Index Space T – Table IND – Index V – view Maximum storage space for a Table Space is 64 million bytes. SQL can be used to Create, Delete, and Update and Query the Objects SQL queries can be executed by the following techniques Application programming Tools like QMF (Query Management Facility) SPUFI (SQL Processor User File Input) DATA TYPES : Integer -- 4 bytes Small int -- 2 bytes Char(n) – N bytes Varchar(n) – N+2 bytes Graph(n) – 2n bytes Vargraph(n)– 2N+2 bytes Date – 10 bytes Time – 8 bytes Timestamp – 26 bytes NORMALIZATION : Arranging the data in the Database in organized manner. 1NF: Avoiding multiple values or set of values in one column. 2NF: Avoiding repeated rows by defining primary key. 3NF: Separating functionally dependent...

RETRIEVE ONLY DUPLICATE ROWS FROM A TABLE: SELECT A, B, C FROM TABLE_A GROUP BY B HAVING COUNT(*) > 1 QUERY: IF THE CHAR FIELD HAS FIRST DIGIT AS NUMBER E.G. 1AA THEN THE VALUE SHOULD BE CONSIDERED AS '1' 12A THEN THE VALUE SHOULD BE CONSIDERED AS '12' 123 THEN THE VALUE SHOULD BE CONSIDERED AS '123' A11 THEN THE VALUE SHOULD BE CONSIDERED AS '0' ANSWER: SELECT CASE SUBSTR(F1,1,3) BETWEEN '000' AND '999' THEN INTEGER(SUBSTR(F_1,1,3)) WHEN SUBSTR(F1,1,2) BETWEEN '00' AND '99' THEN INTEGER(SUBSTR(F_1,1,2)) WHEN SUBSTR(F1,1,1) BETWEEN '0' AND '9' THEN INTEGER(SUBSTR(F_1,1,1)) ELSE 0 END AS OUT How to get the MAX count when group by some other field SELECT USER_UPDATED, COUNT (USER_UPDATED) AS COUNT1 FROM CGDEV4.TWC39 GROUP BY USER_UPDATED HAVING COUNT (USER_UPDATED) >= ALL (SELECT COUNT (USER_UPDATED) FROM CGDEV4.TWC39 GROUP BY USER_UPDATED); How to Subtract the years, months and days separately from DAT...