Explore topic-wise InterviewSolutions in Current Affairs.

TSQ:

• Its LIKE a scratch pad, This will not go to permanent MEMORY unless you explicitly specify.
• This is used if you have page up/page down LOGIC in your CICS screens.
• Program A calling Program B which selects db2 rows, So in program B you can write all your rows FETCHED to a TSQ and come back to program A, now you can read these rows from TSQ rather than going to Db2 every time.
• If you have large amount of data to be passed between to tasks of a transaction you can use TSQ.

TDQ:

• If you want some processing should happen depending on the data that you have read from the screen for example. user wants to print the screen, take the information write it to a TDQ this will trigger a transaction which does the PRINTING.
• Trigger level tells and indicates, after nth record is written to a TDQ it should trigger the transaction associated with it.
• TDQ has to be defined prior to use.

TSQ:

TDQ:

SELECT DISTINCT A.TIMESTAMP_UPDATED, A.USER_UPDATED
FROM CGDEV4.TWC39 A
WHERE 5 =
(SELECT COUNT (DISTINCT B.TIMESTAMP_UPDATED)
FROM CGDEV4.TWC39 B
WHERE B.TIMESTAMP_UPDATED >= A.TIMESTAMP_UPDATED)

SELECT DISTINCT A.TIMESTAMP_UPDATED, A.USER_UPDATED
FROM CGDEV4.TWC39 A
WHERE 5 =
(SELECT COUNT (DISTINCT B.TIMESTAMP_UPDATED)
FROM CGDEV4.TWC39 B
WHERE B.TIMESTAMP_UPDATED >= A.TIMESTAMP_UPDATED)

ANSWER : 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);

SELECT DATE ('2003-10-13') - DEC ('03') YEARS - DEC ('07') MONTHS - DEC ('13') DAYS FROM SYSIBM.SYSDUMMY1;

SELECT DATE ('2003-10-13') - DEC ('2000') YEARS –
DEC ('27') MONTHS - DEC ('45') DAY
FROM SYSIBM.SYSDUMMY1;

SELECT DATE ('2003-10-13') - DEC ('03') YEARS - DEC ('07') MONTHS - DEC ('13') DAYS FROM SYSIBM.SYSDUMMY1;

SELECT DATE ('2003-10-13') - DEC ('2000') YEARS –
DEC ('27') MONTHS - DEC ('45') DAY
FROM SYSIBM.SYSDUMMY1;

ANSWER : DELETE FROM TZCHC TD1 WHERE HOLIDAY_DATE<> (SELECT MIN (HOLIDAY_DATE) FROM TZCHC TD2 WHERE TD1.LONG_DESC = TD2.LONG_DESC AND TD2.LONG_DESC = 'CHRISTMAS DAY’);

Answer : //CREATE EXEC PGM=IKJEFT01,DYNAMNBR=20 //SYSTSPRT DD SYSOUT=* //SYSPRINT DD SYSOUT=* //SYSTSIN DD * DSN SYSTEM(DB2T) RUN PROGRAM(DSNTEP2) PLAN(DSNTEP2) LIB('DB2T.RUNLIB.LOAD') END /* //SYSIN DD * SELECT COUNT(USER_UPDATED) FROM CGDEV4.TWC39 WHERE USER_UPDATED='DPPLSPP' GROUP BY USER_UPDATED /*

SELECT (LENGTH('SUBASH') - LENGTH(REPLACE('SUBASH','S',''))) AS S_COUNT FROM SYSIBM.SYSDUMMY1

SELECT (LENGTH('SUBASH') - LENGTH(REPLACE('SUBASH','S',''))) AS S_COUNT FROM SYSIBM.SYSDUMMY1

DELETE FROM TABLE1 A WHERE A.PRIMARYKEY<>(SELECT MIN(PRIMARYKEY) FROM TZCHC B
WHERE A.COLUMN1= B.COLUMN1);

DELETE FROM TABLE1 A WHERE A.PRIMARYKEY<>(SELECT MIN(PRIMARYKEY) FROM TZCHC B
WHERE A.COLUMN1= B.COLUMN1);

SELECT A.DRIVER_REFERENCE
FROM CGDEV4.TU4ML A
WHERE A.LICENSE_NUMBER = 'POTAPA*355B2' AND
A.DRIVER_REFERENCE =
(SELECT C.CLIENT_REFERENCE
FROM CGDEV4.TZ1SR C
WHERE A.DRIVER_REFERENCE = C.CLIENT_REFERENCE AND
SUBSTR(C.Z_UPPER_FIRST_NAME,1,1) = 'A' AND
SUBSTR(C.Z_UPPER_LAST_NAME,1,3) = 'JZB')

SELECT A.DRIVER_REFERENCE
FROM CGDEV4.TU4ML A
WHERE A.LICENSE_NUMBER = 'POTAPA*355B2' AND
A.DRIVER_REFERENCE =
(SELECT C.CLIENT_REFERENCE
FROM CGDEV4.TZ1SR C
WHERE A.DRIVER_REFERENCE = C.CLIENT_REFERENCE AND
SUBSTR(C.Z_UPPER_FIRST_NAME,1,1) = 'A' AND
SUBSTR(C.Z_UPPER_LAST_NAME,1,3) = 'JZB')

SELECT POLICY_NUMBER, DIGITS (BASE_PREM_TOT)
FROM DB2K.CGIST.TW4T0
If the VALUE of BASE_PREM_TOT is 472.00 then it will be SELECTED as LIKE this 00000047200

SELECT POLICY_NUMBER, DIGITS (BASE_PREM_TOT)
FROM DB2K.CGIST.TW4T0
If the value of BASE_PREM_TOT is 472.00 then it will be selected as like this 00000047200

It is a data structure that must be included in any host-language program using SQL. It is used to PASS feedback about the sql operations to the program. Fields are return CODES, ERROR messages, handling codes and WARNINGS.

It is a data structure that must be included in any host-language program using SQL. It is used to pass feedback about the sql operations to the program. Fields are return codes, error messages, handling codes and warnings.

When a VIEW is CREATED, SYSTEM INFORMATION about the view is STORED in SYSIBM.SYSVIEWS.

When a view is created, system information about the view is stored in SYSIBM.SYSVIEWS.

This INFORMATION can be FOUND in SYSIBM.SYSINDEXES.

This information can be found in SYSIBM.SYSINDEXES.

SYSIBM.SYSDBAUTH

SYSIBM.SYSDBAUTH

The SYSIBM.SYSLINKS table CONTAINS INFORMATION about the links between tables created by REFERENTIAL CONSTRAINTS.

The SYSIBM.SYSLINKS table contains information about the links between tables created by referential constraints.

Included is the name of the database, the table SPACE name, and the IMAGE copy type(full or incremental etc.,) as WELL as the date and time each copy was MADE.

Included is the name of the database, the table space name, and the image copy type(full or incremental etc.,) as well as the date and time each copy was made.

The SYSIBM.SYSCOPY table CONTAINS information about image COPIES MADE of the TABLESPACES.

The SYSIBM.SYSCOPY table contains information about image copies made of the tablespaces.

In the RECLENGTH COLUMN of SYSIBM.SYSTABLES

In the RECLENGTH column of SYSIBM.SYSTABLES

The DB2 catalog is a set of TABLES that contain INFORMATION about all of the DB2 objects(tables, views, PLANS ETC.).

The DB2 catalog is a set of tables that contain information about all of the DB2 objects(tables, views, plans etc.).

Asynchronously.

Asynchronously.

DB2 can be invoked by TSO USERS by USING the DSN RUN command.

DB2 can be invoked by TSO users by using the DSN RUN command.

EXCLUSIVE, UPDATE, and SHARE.

Exclusive, update, and share.

No

No

This is a SEQUENCE of OPERATIONS within a unit of work(i.e., work DONE between COMMIT points).

This is a sequence of operations within a unit of work(i.e., work done between commit points).

This is a seven PART VALUE that consists of a date (yymmdd) and TIME(hhmmss and MICROSECONDS).

This is a seven part value that consists of a date (yymmdd) and time(hhmmss and microseconds).

A STOGROUP is a named collection of DASD VOLUMES to be used by tablespaces and index SPACES of databases. The volumes of STOGROUP MUST be of the same device type.

A STOGROUP is a named collection of DASD volumes to be used by tablespaces and index spaces of databases. The volumes of STOGROUP must be of the same device type.

When an application PROGRAM executes with repeatable read PROTECTION, rows referenced by the program can’t be changed by other PROGRAMS until the program REACHES a COMMIT point.

When an application program executes with repeatable read protection, rows referenced by the program can’t be changed by other programs until the program reaches a commit point.

Each plan is DEFINED UNIQUELY in the SYSIBM.SYSPLANS table to CORRESPOND to the transaction (s) that are to EXECUTE that plan.

Each plan is defined uniquely in the SYSIBM.SYSPLANS table to correspond to the transaction (s) that are to execute that plan.

The RCT is a table that is defined to a DB2/CICS region. It CONTAINS control characteristics which are assembled VIA the DSNCRCT macros. The RCT matches the CICS transaction ID to its associated DB2 authorization ID andplan ID(CICS ATTACHMENT facility).

The RCT is a table that is defined to a DB2/CICS region. It contains control characteristics which are assembled via the DSNCRCT macros. The RCT matches the CICS transaction ID to its associated DB2 authorization ID andplan ID(CICS attachment facility).

A predicate is an element of a SEARCH CONDITION that EXPRESSES or implies a comparison OPERATION.

A predicate is an element of a search condition that expresses or implies a comparison operation.

Pagespace refers to EITHER to an unpartitioned table, to an INDEX SPACE, or to a SINGLE PARTITION of a partitioned table of index space.

Pagespace refers to either to an unpartitioned table, to an index space, or to a single partition of a partitioned table of index space.

This is the UNIT of STORAGE within a table space or indexspace that is accessed by DB2.

This is the unit of storage within a table space or indexspace that is accessed by DB2.

The optimizer is a DB2 COMPONENT that PROCESSES SQL STATEMENTS and SELECTS the access paths.

The optimizer is a DB2 component that processes SQL statements and selects the access paths.

This is a data ITEM that is used in an SQL statement to receive a value or to SUPPLY a value. It MUST be preceded by a COLON (:) to tell DB2 that the VARIABLE is not a column name.

This is a data item that is used in an SQL statement to receive a value or to supply a value. It must be preceded by a colon (:) to tell DB2 that the variable is not a column name.

It will DROP(DELETE) that EXISTING PLAN.

It will drop(delete) that existing plan.

DSNDB07 is where DB2 does its SORTING. It includes DB2’s sort work area and EXTERNAL STORAGE

DSNDB07 is where DB2 does its sorting. It includes DB2’s sort work area and external storage

DCLGEN is a facility that is used to generate SQL statements that DESCRIBE a table or view. These table or view descriptions are then used to check the validity of other SQL statements at precompile time. The table or view declares are used by the DB2I utility DCLGEN to BUILD a host language structure, which is used by the DB2 precompiler to verify that correct column NAMES and data types have been specified in the SQL STATEMENT.

DCLGEN is a facility that is used to generate SQL statements that describe a table or view. These table or view descriptions are then used to check the validity of other SQL statements at precompile time. The table or view declares are used by the DB2I utility DCLGEN to build a host language structure, which is used by the DB2 precompiler to verify that correct column names and data types have been specified in the SQL statement.

They are attributes of COLUMNS, LITERALS, and host variables. The data TYPES are SMALLINT, INTEGER, FLOAT, DECIMAL, CHAR, VARCHAR, DATE and TIME.

They are attributes of columns, literals, and host variables. The data types are SMALLINT, INTEGER, FLOAT, DECIMAL, CHAR, VARCHAR, DATE and TIME.

A data page is a unit of RETRIEVABLE data, either 4K or 32K (depending on how the table is DEFINED), containing USER or CATALOG INFORMATION.

A data page is a unit of retrievable data, either 4K or 32K (depending on how the table is defined), containing user or catalog information.

A DBRM is a DB2 component created by the DB2 pre-compiler CONTAINING the SQL source statements EXTRACTED from the APPLICATION PROGRAM. DBRMs are input to the BIND process.

A DBRM is a DB2 component created by the DB2 pre-compiler containing the SQL source statements extracted from the application program. DBRMs are input to the bind process.

The Data Manager is a DB2 component that manager the physical DATABASES. It invokes other system components, as necessary, to perform detailed functions such as locking, logging, and physical I/O operations (such as SEARCH, retrieval, UPDATE, and index maintenance).

The Data Manager is a DB2 component that manager the physical databases. It invokes other system components, as necessary, to perform detailed functions such as locking, logging, and physical I/O operations (such as search, retrieval, update, and index maintenance).

It is cursor STABILITY that “TELLS” DB2 that database values read by this application are protected only while they are being used. (Changed values are protected until this application reaches the commit point). As soon as a program MOVES from ONE row to another, other programs may read or the FIRST row.

It is cursor stability that “tells” DB2 that database values read by this application are protected only while they are being used. (Changed values are protected until this application reaches the commit point). As soon as a program moves from one row to another, other programs may read or the first row.

CONCURRENCY is what allows more than one DB2 APPLICATION process to access the same data at essentially the same TIME. Problems may OCCUR, such as lost updates, access to uncommitted data, and un-repeatable READS.

Concurrency is what allows more than one DB2 application process to access the same data at essentially the same time. Problems may occur, such as lost updates, access to uncommitted data, and un-repeatable reads.

COMMIT will ALLOW data changes to be permanent. This then permits the data to be accessed by other units of work. When a COMMIT occurs, LOCKS are freed so other applications can REFERENCE the just committed data.

COMMIT will allow data changes to be permanent. This then permits the data to be accessed by other units of work. When a COMMIT occurs, locks are freed so other applications can reference the just committed data.

It is a TYPE of index that (1) LOCATES table ROWS and (2) determines how rows are GROUPED TOGETHER in the tablespace

It is a type of index that (1) locates table rows and (2) determines how rows are grouped together in the tablespace

CLOSE PHYSICALLY closes the tablespace when no ONE is working on the OBJECT. DB2 (release 2.3) will LOGICALLY close tablespaces.

CLOSE physically closes the tablespace when no one is working on the object. DB2 (release 2.3) will logically close tablespaces.

There are FOUR BUFFER POOLS: BP0, BP1, BP2, and BP32.

There are four buffer pools: BP0, BP1, BP2, and BP32.

A buffer pool is MAIN storage that is reserved to satisfy the buffering requirements for ONE or more TABLESPACES or indexes, and is made up of either 4K or 32K pages.

A buffer pool is main storage that is reserved to satisfy the buffering requirements for one or more tablespaces or indexes, and is made up of either 4K or 32K pages.

The BUFFER manager is the DB2 component responsible for physically TRANSFERRING data between an external medium and (virtual) storage (performs the actual I/O operations). It minimizes the amount of PHYSICAL I/O ACTUALLY PERFORMED with sophisticated buffering techniques(i.e., read-ahead buffering and look-aside buffering).

The buffer manager is the DB2 component responsible for physically transferring data between an external medium and (virtual) storage (performs the actual I/O operations). It minimizes the amount of physical I/O actually performed with sophisticated buffering techniques(i.e., read-ahead buffering and look-aside buffering).

A BASE table is a real table - a table that physically EXISTS in that there are PHYSICAL STORED records.

A base table is a real table - a table that physically exists in that there are physical stored records.