What is the difference in between dsm.opt and dsm.sys file?

Dsm.opt file is the client file which contains the tsmservername pointing to dsm.sys.

Dsm.sys normally used in UNIX environment contains the server stanzas and the parameters like node name, management class and password access options.

What exactly the role of recovery log?

Storing the transaction, which is not committed to DB and are, required for consistency of TSM DB.

What is difference in between label and checkin?

Labeling is naming vol. check in is inserting the vol to slot.

How you will prepare scratch volume?

label libvol volumename

Tell about media management you are doing?

Preparing volumes. Labeling. Checkin, check out etc.

Can you brief your responsibilities on TSM and daily activities?

Check Schedule, DB & Recover log, managing volumes. Explained earlier

What kind of tape library is attached to your TSM server?

 IBM 3584

Tell Something on Migration?

Transferring data from disk to tape pool.

What is collocation and advantages of it?

Storing data of a client at minimal volumes. This will help in faster restores as less media mounts will be required during restore.

What is Reclamation?

It is the process of moving the fragment data from one or more volumes to a different volume, so that the volumes being reclaimed can be used as scratch volumes in TSM again. By using this technique we can save a lot of space in TSM. This process is controlled by reclaim threshold parameter of the storage pool.

What are the storage devices you have worked on?

Library IBM 3584, 3582   etc.

What are the Primary and Secondary Storage Pool in your environment?

Explain the Disk Pool and Tape Pools        

What is maximum size of recovery log?

20 GB in TSM 5.3

If your DBSPACE is full then what you will do?

Add database volumes by the command line as TSM server will not work if     DB is full.

DEFINE STG?

DEFINE STGPOOL Primary SEQUENTIAL Access

DEFINE STGPOOL — copy sequential access

DEFINE STGPOOL — ACTIVE SEQUENTIAL Access

DEFINE VOLUME

EXPIRE INVENTORY

Task Run the expiration process for two hours.

Command: expire inventory duration=120

Task: Run the expiration process on files only.

Command: expire inventory skipdirs=yes

Check the scheduler Service is running or not?

                                Windows   -           Start > run > services.msc > check the TSM SCHEDULER

                                UNIX       -            ps -ef | grep dsmc

What is CLEAN DRIVE?

CLEAN DRIVE library_name drive_name

Task: You have already defined a library named AUTOLIB by using the DEFINE LIBRARY command, and you have already checked a cleaner cartridge into the library using the CHECKIN LIBVOL command. Inform the server that TAPEDRIVE3 in this library requires cleaning.

Command: clean drive autolib tapedrive3

ACCEPT DATE Syntax: ACCept Date

Parameters: None

Examples Task: Allow the server to accept the current date as the valid date.

Command: accept date

What is CLOPTSET?

Syntax  DEFine CLOptset option_set_name DESCription = description

Task: Define a client option set named ENG.

Command: define cloptset eng

DEFine CLIENTOpt option_set_name option_name option_value __ Force = No Force = No Yes SEQnumber = number

Task: Add a client option to the option set WINSPEC to exclude a temporary internet directory from backup services. When you use the EXCLUDE or INCLUDE option with file names that contain spaces, put single quotes around the file specification, then double quotes around the entire option.

Command: define clientopt winspec inclexcl "exclude.dir’*:\...\Temporary Internet Files’"

Define RECLAIM STG?

RECLaim STGpool pool_name THreshold = number _ RECLAIM STGPOOLChapter 2. Administrative commands 887

DUration = minutes Wait = No Wait = No Yes __ OFFSITERECLAIMLimit =number_of_volumes

What is MIGRATE STG?

MIGrate STGpool pool_name LOwmig = number __ DUration = minutesREClaim = No REClaim = No Yes Wait = No Wait = No Yes

What is MACRO Process ?

Create a macro file named AUTHRG, containing substitution variables, to register and grant authority to a new administrator.

Write the macro as follows:

/* Register and grant authority to a new administrator */

REGister Admin %1 %2 -             /* Enter userid and password */

CONtact=%3                  /* Enter contact info (in quotes if nec.) */

GRant AUTHority    %1           - /* Server uses variable already */ - /* defined by you */

CLasses=%4                /* Enter the privilege class  */

Enter the values you want to pass to the server to process the command when you run the macro.

Command: macro authrg.mac jones passwd x1235 Policy

DB SPACE TRIGGER?

DEFine SPACETrigger DB LOG STG Fullpct = 80 Fullpct = percent __ SPACEexpansion = 20 SPACEexpansion = percent EXPansionprefix = prefix _DEFINE SPACETRIGGER 316 IBM Tivoli Storage Manager for Windows: Administrator’s Reference

MIRRORprefixes = prefix prefix 1, prefix 2 MAXimumsize = megabytes __ STGPOOL = storage_pool_name

Task: Set the database space trigger to 80 percent. When the database reaches this value, allocate a volume that is twice the current size. Use space in the j:\adsmserv\ directory.

Command: define spacetrigger db spaceexpansion=100 expansionprefix=j:\adsmserv\

Task: Set the database space trigger to 80 percent. When the database reaches this value, allocate a volume that is twice the current size. Use space in the j:\adsmserv\ directory. Create copy volumes in separate directories

Command: define spacetrigger db spaceexpansion=100 expansionprefix=j:\adsmserv\ mirror prefixes = "k:\adsmserv\,l:\adsmserv\"

Task: Setup a storage pool space trigger for increasing the amount of space in a storage pool by 25 percent when it is filled to 80 percent utilization of existing volumes. Space will be created in the directories associated with the device class.

Command: define spacetrigger stg spaceexpansion=25 stgpool=file

Task: Setup a space trigger for the WINPOOL1 storage pool to increase the amount of space in the storage pool by 40 percent when it is filled to 80 percent utilization of existing volumes.

Command: define spacetrigger stg spaceexpansion=40 stgpool=winpool1

What is SERVER GROUP MEMBER ?

Use this command to add a server as a member of a server group. You can also add one server group to another server group. A server group lets you route commands to multiple servers by specifying only the server group name.

Syntax: DEFine GRPMEMber group_name _ , member_name

Task 1: Define the server SANJOSE to server group CALIFORNI

Command: define grpmember california sanjose

DEFINE SERVER GROUP?

Use this command to define a server group. A server group lets you route commands to multiple servers by specifying only the group name. After defining the server group, add servers to the group by using the DEFINE GRPMEMBER command.

Syntax: DEFine SERVERGRoup group_name DESCription = description

Task: Define a server group named WEST_COMPLEX.

Command: define servergroup west_complex

What is DB COPY?

Syntax: DEFine DBCopy volume_name copy_volume_name __ Wait = No Formatsize = megabytes Wait = No Yes

What is NODE GROUP?

Define NODEGroup group_name DESCription = description __ Parametersgroup_?

Task: Define a node group named group1. Command define nodegroup group1

NODE GROUP MEMBER: DEFine NODEGROUPMember group_name node_name

Task: Define two members, node1 and node2, to a node group, group1.

Command: define nodegroupmember group1 node1,node2

What is DISMOUNT VOLUME (Dismount a volume by volume name)?

Use this command to dismount an idle volume by volume name. If a drive cannot dismount the volume, manual intervention is required. Privilege class To issue this command, you must have system privilege or operator privilege.

Syntax: DISMount Volume volume_name

Syntax for a library in a LAN, not used for NDMP operations

DEFine LIBRary library_name LIBType = MANUAL LIBType = MANUAL SCSI A 349X B EXTernal ACSLS C RSM D __ RESETDrives = Yes (1) RESETDrives = Yes No AUTOLabel = Yes (2) AUTOLabel = No Yes OVERWRITE __ DEFINE LIBRARY240 IBM Tivoli Storage Manager for Windows: Administrator’s Reference

A (SCSI): AUTOLabel = No Yes OVERWRITEACKUP SET

DEFine BACKUPSET node_name node_group_name backup_set_name_prefix __ DEVclass = device_class_name _ , VOLumes =volume_names __ RETention = 365 RETention = days NOLimit DESCription =description __ _ WHEREDATAType = ALL , WHEREDATAType = FILE IMAGE APPL

TOC = PREFERRED YES NO __ TOCMGmtclass = class_name

Task: define the PERS_DATA backup set that belongs to client node JANE to the server running this command. Retain the backup set on the server for 50 days. Specify that volumes VOL001 and VOL002 contain the data for the backup set. The volumes are to be read by a device that is assigned to the AGADM device class. Include a description.

Command: define backupset jane pers_data devclass=agadm volumes=vol1,vol2 retention=50 description="sector 7 base "

What is AUDIT LIBRARY?

AUDIT LIBRary library_name CHECKLabel = Yes CHECKLabel = Yes/ Barcode

Task: Audit the EZLIFE automated library

Command: audit library ezlife

What is RESTORE VOLUME (Restore primary volume data from a copy pool or an active-data pool)?

RESTORE Volume _ , volume_name COPYstgpool = copy_pool_name  ACTIVEDATAOnly = No Yes A __ NEWstgpool = new_primary_pool_nameMAXPRocess = 1 MAXPRocess = number __ Preview = No Preview = No Yes Wait = No Wait = No Yes __ A (Yes): ACTIVEDATAPool = active-data_pool_name

Task: Restore files stored on volume PVOL2 in primary storage pool PRIMARY_POOL

Command: restore volume pvol2

Restore files stored on volume VOL001 in primary pool PRIMARY_POOL from active-data pool ADP1.

Command: restore volume vol001 activedataonly=yes activedatapool=adp1

To Restore a primary stgpool from copy storage pool or active datapools?

RESTORE STGpool primary_pool_name COPYstgpool = copy_pool_name _

ACTIVEDATAOnly = No ACTIVEDATAOnly = No Yes A __ NEWstgpool =new_primary_pool_name MAXPRocess = 1 MAXPRocess = number __ Preview = No Preview = No Yes Wait = No Wait = No Yes  A (Yes):ACTIVEDATAPool = active-data_pool_name

Task: Restore files from any copy storage pool to the primary storage pool, PRIMARY_POOL. Command: restore stgpool primary_pool

Task: Restore files from active-data pool ADP1 to the primary storage pool PRIMARY_POOL. Command: restore stgpool primary_pool activedataonly=yes activedatapool=adp1

What is Classic scheduling?

In classic scheduling u can set the schedule frequency based on period of hours,days,weeks, months, years

In enhanced scheduling u can set the schedule frequency like months, dayofmonth, dayofweek,weekof month.

How to restorea backup set?

Restore backupset sourcefilespec destinationfilespec backupsetname=bksetname  loc= server/file/table

What is Database backup trigger ?

Def dbbackuptrigger logpctutil=%  deviceclass=deviceclassname  noofincrementals=number

TIVOLI STORAGE MANAGER MAINTENANCE PLAN?

Back up the primary storage pool to copy storage pool

Backup the tsm server database

Update the disaster recovery plan

Move the data from disk to tape

Remove the expired data from server storage

Reclaim the fragmented tapes by consolidating active data

when full back up is required?

For the first time backup, if there are 32 incremental backups since the last  full backup After changing the log mode to roll forward, after changing the data base size Snap shot backup :snap shot backup does not empty the recovery log, It doesn’t interrupt the full and incremental back up.

How to configure the schedule service in linux ?

Using dsmj

To start the schedule service using ./dsmcad

How to delete last two days data base backup information?

delete volhist type=dbb begindate=-4 enddate=-2

What is the process of Checkout library volume ?

>>-CHECKOut LIBVolume--library_name----+-volume_name-+---------->

                                        '-| A |-------'

    .-REMove--=--Bulk-----.  .-CHECKLabel--=--Yes-----.

 >--+---------------------+--+------------------------+---------->

    '-REMove--=--+-Yes--+-'  '-CHECKLabel--=--+-Yes-+-'

                 +-No---+                     '-No--'

                 '-Bulk-'

    .-FORCE--=--No------.

 >--+-------------------+---------------------------------------><

    '-FORCE--=--+-No--+-'

                '-Yes-'

 A :

 |--+-VOLRange--=----volume_name1,volume_name2---+---------------|

 SCSI Library

 >>-CHECKOut LIBVolume--library_name----+-volume_name-+---------->

                                       '-| A |-------'

    .-REMove--=--Bulk-----.  .-CHECKLabel--=--Yes-----.

 >--+---------------------+--+------------------------+---------->

    '-REMove--=--+-Yes--+-'  '-CHECKLabel--=--+-Yes-+-'

                 +-No---+                     '-No--'

                 '-Bulk-'

    .-FORCE--=--No------.

 >--+-------------------+---------------------------------------><

    '-FORCE--=--+-No--+-'

                '-Yes-'

 A :

 |--+-VOLRange--=----volume_name1,volume_name2---+---------------|

Remove :specifies the server tries to move the volumes out of the library and into the convenience i/o station or entry exit ports. this parameter is optional. default is bulk. possible values are yes, no, bulk

What is SEARCH Process in TSM?

Specifies whether the server searches the library to find volumes that were not checked in. This parameter is optional. The default is NO.

Possible values are:

No:     Specifies that only the named volume is checked into the library. 

For SCSI libraries: The server issues a request to have the volume inserted into a cartridge slot in the library or, if available, into an entry port. The cartridge slot or entry port is identified by its element address. For 349X libraries: The volume could already be in the library, or you could put it into the I/O station when prompted.

Yes:   Specifies that the server searches the library for volumes to be checked in. You can use the VOLRANGE or VOLLIST parameter to limit the search. When using this parameter, consider the following 

Restrictions:

          If the library is shared between applications, the server could examine a volume required by another application. For 349X libraries, the server queries the library manager to determine all volumes that are assigned to the SCRATCH or PRIVATE category and to the INSERT category.

For SCSI libraries, do not specify both SEARCH=YES and

CHECKLABEL=NO in the same command.

Bulk: Specifies that the server searches the library's entry/exit ports for volumes that can be checked in automatically. This option only applies to SCSI libraries.

Notes: 1. Do not specify both CHECKLABEL=NO and SEARCH=BULK.

             2. You can use the VOLRANGE or VOLLIST parameter to limit the Search

What is password expire ?

Specifies the no of days the password is valid. You can specify the password limit 0 t0 9999

How can u check the activitylog data for last two days ?

q actlog begind=-2

what will u do when the tsm server crashed ?

Try to restart the server if it is not starting the create db and log volumes using dsmfmt

Then try to restart the server, if it is still failing to start then restore the database using

dsmserv restore db up to current state.

dsmserv restore db todate –today totime=now

How can check the client and admin schedule status,if any failures investigate the issue and rerun the job modules?

Using   q event * * exception=yes begindate=-1

Q event * type=admin

Search dsmsched.log and dsmerror.log in client machine

If required take the manual backup

If required take the db backup, expire interval, reclamation, migration

Registering and removing the nodes

Installing client application like b/a client, tdp sql

Change node name

If require restore the client data as per client requirement.

Which type of Daily issues of Tsm Administrator (Health check of the Tsm server)?

check the usage of db, recovery log,storage pools

If required create db and recovery log volumes

what is macro ?

macro is a file that can be stored in administrative client machine.

Create a macro to enter a command can be helpful whwn u want to issue a command repeatedly to issue the command that contain several parameters

Synatax: macro macroname substitution value.

Macro can be changed dynamically.

What is Reuse of backup tapes ?

list the volumes that bring to onsite

Move the tapes from offsite to onsite

Update their status to scratch

Check in tnto the library

What is offsite tape tape to onsite ?

list the volumes to be send to onsite

update the volume location to onsite

move the tapes from offsite to onsite

checkin into the library

What is onsite tape management to offsite ?

Backup up the data to copy storage pools

List out the volumes to be send to offsite

Update their location to offsite

Checkout from the library

What is the procees to store the data on a tape?

Label the data and check in into the library

Use that tapes

Checkout form the library

Move that tapes to offsite

Bring the offsite tapes to onsite and reuse it

What is administrative interface ?

An interface that is used to manage multiple tsm servers.

Admincenter: admin center is web interface that is used to centrally configure and manage the multiple tsm severs.

Management console: it is used to initialize the server.

B/A GUI: it is a client GUI to take the backup and restore operations.

Command line:administrative command line: is invoked by dsmadmc

B/A command line: it is invoked by dsmc

Features of TSM?

progressive incremental backup methodology

Tape resource sharing

Lan free data transfer.

Network free data recovery: supports high speed client data directly from tape.

What is auditing library ?

Auditing library to recover the mismatches between home element addresses between the database inventory and library inventory.

Command: audit library libraryname checklabel =barcode.

What is Audit volume ?

We will audit the volume when the volume is damaged

if the volume is not being accessed for a long period of time, any read errors occurs while accessing the volume.

audit volume volume name fix=yes/no

Yes: any database record that refers to a file are found with logical inconsistency that file is marked as damaged.

No: any data base record that refers to a file are find with logical inconsistency the data base records that refer to a file are deleted from the data base.

What are the types of backups ?

Full backup: backup all the files at every backup.

Incremental backup:backup the files which are changed since the last backup.

Selective: backup the files which are changed since last backup regardless whether they are changed since the last backup.

Image backup: full volume backup

Image with differential backup: full volume backup which can be folllwed by subsequent differential backup.

Adaptive subfile backup: back up only part of the file that was changed since the last backup.

Group backup: backup the files that u specified as a named groupfiles can be one or more filespaces.

Journal based backup: in this method first u have to install journal engine .journal engine keeps the changed files.

Image backup: dsmc backup image H:

Compression: this compresses the file before u send them to server.compressing the file reduces the data storage of your backup dat.

TSM modes ?

mode=modified/absolute

modified=back up only the only files which are changed since last backup,

absolute: backup the files regardless whether it modified or not since the last backup.

What is serialization ?

Serialization a concept for how to treat the client data while taking the backup .

Serialization parameters are static, shared static, dynamic, shared dynamic. By default it is shared static.

How can u restore a file to a particular date and time ?

restore  pitdate=date pittime=time  filepath

What are the types of backups in TDP domino and tdp aql ?

In tdp domino  sel , inc, in tdp sql full,inc,log, full plus log,differential.

What are the types of backups ?

Full backup,incremental backup, sel, differential backup, imge backup,journal based backup,group backup.

Move node data command ?

Used to move entire file spaces of a node from  one storage fool to another storage pool.

Command:Move nodedata nodename  tostg=stgname

What is move data command ?

To move the data from one volume to another volume with ina same storage pool or to another storage pol. Movedata   volumename  fromstg=stgname  tostg=stgname

What is Backup set ?

Backupset is a group of active files that can be stored in portable medi the advantage of backupset is While restoring to the client we can restore it without tsm server and network.

Commnd: Define backupset nodename  backupsetname  devc=dviceclassname  volume=volnme

How to define a db copy ?

Define dbcopy path1  path2

What is space trigger ?

The use of space trigger is it automatically extend the db and recovery log size when specific percentage is reached.

Define spacetrigger db  fullpctutil= %  spaceexpansion= % expansionprefix=path mirrorprefix = path  Size=  mb

what is recovery log ?what are the modes of recory log ?

A recovery log is what ever transactions happened to the database tht transaction related records are recorded in the recovery log. one the data is committed to the database recovery becomes empty. this is normal mode.

Roll forward mode: once the transaction is committed to the database all the transaction related records are stored in the recovery log until we take the Db backup.

The advantage of normal mode it requires less memory

The disadvantage is we can’t recover the database to the most current state.

The advantage of roll forward is we can recover the database to the current state hwen it was went down,The disadvantage is it requires more space.

what is cloptset ?

Cloptset is mainly for multiple clients,if u want to exclude the same directory  from multiple clients while taking the backup then go for cloptset.

Define cloptset cloptsetname

Define clientopt optionsetname optionvalue .there are 49 client option sets(like INCLEXCL)

what time u take the DB backup ?how many ways u can take the DB backup ?

at day time and every 12 hours ,

at scheduled time

manually,

Based on the trigger level

what is collactaion ?

Collocation is the process of sending the node data or group of nodes or file space data to minimum no of tapes,

The advantage of collcaton is fast restoration,

Disadvantage is  migration is slow.

Define collocation collacgroupname

Def collacmember collacgroupname node1,node2,node3………

Configure drm settings.

Daily operations:

1)Back up the primary storage pool to copy storage pool

2)Back up the tsm database

3)move drm states

4)run prepare command

5)move drm media to offsite

6)get the media from offsite.

DRM CYCLE:

Mountable to not mountable

Not mountable to courier

Courier to vault

Vault to vault+retrieve

Vault+retrieve to  onsiteretreive

How will you move copy storage pools to offsite in DRM?

step1: first register the licence with TSM

step2: define copy storage pool

What is DRM?

Disaster Recovery Manager

How will you checkin & Chechout volumes in library?

checkin libvolume library_name volume_name search=no/yes/bulk status=private/scratch 

Checkout libvolume library_name volume_name remove=bulk/yes/no

What is CHECK PENDING?

When a table is LOADed with ENFORCE NO option, then the table is left in CHECK PENDING status. It means that the LOAD utility did not perform constraint checking.

What is COPY PENDING status?

A state in which, an image copy on a table needs to be taken, In this status, the table is available only for queries. You cannot update this table. To remove the COPY PENDING status, you take an image copy or use REPAIR utility.

When do you use the IMAGECOPY?

To take routine backup of tables

After a LOAD with LOG NO

After REORG with LOG NO

What is IMAGECOPY?

It is full backup of a DB2 table which can be used in recovery.

What is REORG? When is it used?

REORG reorganizes data on physical storage to reclutser rows, positioning overflowed rows in their proper sequence, to reclaim space, to restore free space. It is used after heavy updates, inserts and delete activity and after segments of a segmented tablespace have become fragmented.

When will you chose to run RUNSTATS?

After a load, or after mass updates, inserts, deletes, or after REORG.

What is RUNSTATS?

A DB2 utility used to collect statistics about the data values in tables which can be used by the optimizer to decide the access path. It also collects statistics used for space management. These statistics are stored in DB2 catalog tables.

How do you insert a record with a nullable column?

To insert a NULL, move -1 to the null indicator

To insert a valid value, move 0 to the null indicator.

What does it mean if the null indicator has -1, 0, -2 ?

-1  : the field is null

  0 : the field is not null

-2  : the field value is truncated

How do you retrieve the data from a nullable column?

Use null indicators. Syntax ... INTO :HOSTVAR:NULLIND

What is index cardinality?

The number of distinct values a column or columns contain.

What is a synonym ?

Synonym is an alternate name for a table or view used mainly to hide the leading qualifier of a table or view.. A synonym is accessible only by the creator.

What do you mean by NOT NULL WITH DEFAULT? When will you use it?

This column cannot have nulls and while insertion, if no value is supplied then it will have zeroes, spaces or date/time depending on whether it is numeric, character or date/time.

Use it when you do not want to have nulls but at the same time cannot give values all the time you insert this row.

What is the difference between SYNONYM and ALIAS?

SYNONYM: is dropped when the table or tablespace is dropped. Synonym is available only to the creator.

ALIAS: is retained even if table or tablespace is dropped. ALIAS can be created even if the table does not exist. It is used mainly in distributed environment to hide the location info from programs. Alias is a global object & is available to all.

What are the disadvantages of using VARCHAR?

• Can lead to high space utilization if most of the values are close to maximum.
• 
  
• Positioning of VARCHAR column has to be done carefully as it has performance implications.
• 
  
• Relocation of rows to different pages can lead to more I/Os on retrieval.

When would you prefer to use VARCHAR?

When a column which contains long text, e.g. remarks, notes, may have in most cases less than 50% of the maximum length.

How do I create a table MANAGER ( EMP#, MANAGER) where MANAGER is a foreign key which references to EMP# in the same table? Give the exact DDL.

First CREATE MANAGER table with EMP# as the primary key. Then ALTER it to define the foreign key. When is the authorization check on DB2 objects done - at BIND time or run time? At run time.

What are the 2 sqlcodes that are returned?

100 ( for successful completion of the query ), 0 (for successful COMMIT if AUTOCOMMIT is set to Yes).

What is a collection?

A user defined name that is the anchor for packages. It has not physical existence. Main usage is to group packages. In SPUFI suppose you want to select max. of 1000 rows , but the select returns only 200 rows.

What are the advantages of using a PACKAGE?

·   Avoid having to bind a large number of DBRM members into a plan

·   Avoid cost of a large bind

·   Avoid the entire transaction being unavailable during bind and automatic rebind of a plan

·   Minimize fallback complexities if changes result in an error.

What are PACKAGES?

They contain executable code for SQL statements for one DBRM.

What happens to the PLAN if index used by it is dropped?

Plan is marked as invalid. The next time the plan is accessed, it is rebound.

What else is there in the PLAN apart from the access path?

PLAN has the executable code for the SQL statements in the host program.

What is ACQUIRE/RELEASE in BIND?

Determine the point at which DB2 acquires or releases locks against table and tablespaces, including intent locks.

What is a DBRM, PLAN?

DBRM: DataBase Request Module, has the SQL statements extracted from the host language program by the pre-compiler.

PLAN: A result of the BIND process. It has the executable code for the SQL statements in the DBRM.

What is ALTER?

SQL command used to change the definition of DB2 objects.

Can I use LOCK TABLE on a view?

No. To lock a view, take lock on the underlying tables.

What are the various locks available?

SHARE, EXCLUSIVE, UPDATE

What is lock escalation?

Promoting a PAGE lock-size to table or tablespace lock-size when a transaction has acquired more locks than specified in NUMLKTS. Locks should be taken on objects in single tablespace for escalation to occur.

What is normalization and what are the five normal forms?

Normalization is a design procedure for representing data in tabular format The five normal forms are progressive rules to represent the data with minimal redundancy.

What is a DB2 bind?

A DB2 bind is a process that builds an access path to DB2 tables.

What is auditing?

Recording SQL statements that access a table Specified at table creation time or through alter.

How do I create a table MANAGER ( EMP#, MANAGER) where MANAGER is a foreign key which references to EMP# in the same table?

Give the exact DDL

First CREATE MANAGER table with EMP# as the primary key Then ALTER it to define the foreign key.

What are the disadvantages of using VARCHAR?

1. Can lead to high space utilization if most of the values are close to MAXIMUM.

2. Positioning of VARCHAR column has to be done carefully as it has performance implications.

3. Relocation of rows to different pages can lead to more I/Os on retrieval.

When would you prefer to use VARCHAR?

When a column which contains long text, eg remarks, notes, may have in most cases less than 50% of the maximum length.

What do you mean by NOT NULL WITH DEFAULT? When will you use it?

This column cannot have nulls and while insertion, if no value is supplied then it wil have zeroes, spaces or date/time depending on whether it is numeric, character or date/time Use it when you do not want to have nulls but at the same time cannot give values all the time you insert this row.

What is the difference between SYNONYM and ALIAS?

SYNONYM: is dropped when the table or table space is dropped Synonym is available only to the creator ALIAS: is retained even if table or table space is dropped ALIAS can be created even if the table does not exist It is used mainly in distributed environment to hide the location info from programs Alias is a global object & is available to all.

What is a SYNONYM?

Synonym is an alternate name for a table or view used mainly to hide the leading Qualifier of a table or view A synonym is accessible only by the creator.

What is filter factor?

One divided by the number of distinct values of a column.

What are simple, segmented and partitioned table spaces ?

Simple Tablespace: Can contain one or more tables Rows from multiple tables can be interleaved on a page under the DBAs control and maintenance.

Segmented Tablespace:Can contain one or more tables Tablespace is divided into segments of 4 to 64 pages in increments of 4 pages. Each segment is dedicated to single table. A table can occupy multiple segments.

Partitioned Tablespace:Can contain one table Tablespace is divided into parts and each part is put in a separate VSAM dataset.

What is FREEPAGE and PCTFREE in TABLESPACE creation?

PCTFREE: percentage of each page to be left free

FREEPAGE: Number of pages to be loaded with data between each free page.

What is an inner JOIN, and an outer JOIN ?

Inner Join: combine information from two or more tables by comparing all values that meet the search criteria in the designated column or columns of one table with the entire clause in corresponding columns of the other table or tables. This kind of join which involve a match in both columns are called inner joins.

Outer join: is one in which you want both matching and non-matching rows to be returned. DB2 has no specific operator for outer joins; it can be simulated by combining a join and a correlated sub query with a UNION.

What are the 4 environments which can access DB2 ?

·         TSO

·         CICS

·         IMS

·         BATCH

Is View Updatate is possible ?

Not all of them Some views are updatable eg single table view with all the fields or mandatory fields Examples of non-updatable views are views which are joins, views that contain aggregate functions(such as MIN), and views that have GROUP BY clause.

What does the SQLCODE -818 pertain to?

This is generated when the consistency tokens in the DBRM and the load module are different.

What is SQL-811?

SELECT statement has resulted in retrieval of more than one row.