ChannelDB2

How to start work as DB2 DBA in DWH team without KT-part1

How to start work as DB2 DBA in DWH team without KT-part1

This is as per my knowledge only.

Different kinds of DBA are

1.Unix admin DBA

2.Application DBA

3.DBA

4. Developer DBA

UNIX admin DBA: Unix administrators basically do the SO (Linux/hp/aix) installation and later if the project/company is small they will do the rest of the installation for Database DB2 and also for the applications like (informatics/DataStage/Business object..etc) for them they fallow the installation document which is provide by IBM/Oracle ..etc.

Application DBA: for Application DBA the server will be ready with OS installed and all the hardware configured by UNIX administrator. The Application DBA will just install the DB2 database and will maintain the Database application up and running in the server.

DBA: For DBA all the UNIX server and DB2 software will be installed and ready. Some case even the Database will also be installed and ready. He needs to do all the DBA activities for the project

Developer DBA: for Developer DBA all the UNIX server and DB2 software will be installed and ready. Some case even the Database will also be installed and ready. The developer DBA will be either given SQL/java coding  for the DB2 database software itself or to do the SQL coding of the DB2 database for the applications.

If you are a UN-expect or accidental DBA for DWH project. Please fallow the below to start the DBA activities.

Know the environment

To work and to find the problems in the current environment .we need to find the full details of the DB, how many DB are there and how it is working. What kind of backups going on ..etc

1. Login to the server using db2admin user (db2inst1)

2. db2 list db directory

This will show all the list of database. Please collect all the DB name and keep the info. Also it will display the path where the Db is installed. please keep this information also.

System Database Directory

7          

7      Number of entries in the directory = 2

7          

7      Database 1 entry:         

7        Database alias                       = SAMPLE    

7        Database name                        = SAMPLE    

7        Database drive                       = /home/smith    

7        Database release level               = 8.00    

7        Comment                              =    

7        Directory entry type                 = Indirect    

7        Catalog database partition number    = 0     

7        Alternate server hostname            = montero

7        Alternate server port number         = 29384

7    

7      Database 2 entry:         

7        Database alias                       = TC004000

7        Database name                        = TC004000

7        Node name                            = PRINODE

7        Database release level               = a.00

7        Comment                              =

7        Directory entry type                 = LDAP

7        Catalog database partition number    = -1

7        Gateway node name                    = PRIGW

7        Alternate server node name           =

7        Alternate server gateway node name   = ALTGW

Things to be done

1. Please make sure that /home(where the DB is installed have sufficient free space)

2. Connect to the Database

DB2 connect to sample

Or

Db2 connect to sample user db2inst1 using password

db2 => connect to sample

 

   Database Connection Information

 

 Database server        = DB2/6000 8.2.0

 SQL authorization ID   = MELNYK

 Local database alias   = SAMPLE

 

 

DB2 configuration file:

Db2 have 2 type of configuration file where the Database configuration parameters can be edited.

1. Instance configuration file:dbmcfg

2. Database configuration file : dbcfg

Db2 get dbmcfg

db2 =>

  • get dbm cfg - Displays the file 
  • update dbm cfg using aslheapsz 30 - Sets the heap size to 30.

Db2 get db cfg-Display the DB configuration file.

db2 =>

  • get db cfg - Displays the file 
  • update db cfg for sample using sortheap 1000 - Sets the sortheap size to 1000.

 

Db2 start/stop commands

1.db2 list application show details

This will show the list of application and users connected to the Database

2.db2 force application all

This will kill all the application/users connected to the database and instance

3.db2stop

This will stop the instance

4.db2start

This will start the instance

5.db2 drop database sample

This will drop the database

6.DB2exfmt - Explain Table Format Tool

7.DB2icrt - Create an instance

8.DB2idrop - Dropan instance

9.DB2ilist - List instances

10.DB2imigr - Migrate instances

11.DB2iupdt - Update instances

12.db2licm -a db2entr.lic

13.db2 get instance

This will display the current instance

14.db2

This Db2licm - Installs licenses file for product

13.db2 list tablespace show details

This will list all the tablesspace created for the particular sample database.

 14. If you are performing a DWH load operation and you CTRL-C out of it or the server/network connection failed  then tablespace is left in a load pending state. The only way to get out of it is to reload the data with a terminate statement.

First to view tablestate:

Db2 list tablespaces show detail will display the tablespace is in a load pending state.

Db2tbst <tablespace state>

Here is the original query

Db2 "load from '/usr/seela/a.del' of del insert into A";

If you break out of the load illegally (ctrl-c), the tablespace is left load pending.

To correct:

Db2 "load form '/usr/seela/a.del' of del terminate into A";

This will return the table to it's original state and roll back the entries that you started loading.

If you try to reset the tablespace with quiesce, it will not work . It's an integrety issue

The following are two sample outputs from LIST TABLESPACES SHOW DETAIL.

 
           Tablespaces for Current Database
 Tablespace ID                        = 0
 Name                                 = SYSCATSPACE
 Type                                 = System managed space
 Contents                             = Any data
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 895
 Useable pages                        = 895
 Used pages                           = 895
 Free pages                           = Not applicable
 High water mark (pages)              = Not applicable
 Page size (bytes)                    = 4096
 Extent size (pages)                  = 32
 Prefetch size (pages)                = 32
 Number of containers                 = 1
 Tablespace ID                        = 1
 Name                                 = TEMPSPACE1
 Type                                 = System managed space
 Contents                             = Temporary data
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 1
 Useable pages                        = 1
 Used pages                           = 1
 Free pages                           = Not applicable
 High water mark (pages)              = Not applicable
 Page size (bytes)                    = 4096
 Extent size (pages)                  = 32
 Prefetch size (pages)                = 32
 Number of containers                 = 1
 Tablespace ID                        = 2
 Name                                 = USERSPACE1
 Type                                 = System managed space
 Contents                             = Any data
 State                                = 0x000c
   Detailed explanation:
     Quiesced: EXCLUSIVE
     Load pending
 Total pages                          = 337
 Useable pages                        = 337
 Used pages                           = 337
 Free pages                           = Not applicable
 High water mark (pages)              = Not applicable
 Page size (bytes)                    = 4096
 Extent size (pages)                  = 32
 Prefetch size (pages)                = 32
 Number of containers                 = 1
 State change tablespace ID           = 2
 State change object ID               = 3
 Number of quiescers                  = 1
   Quiescer 1:
     Tablespace ID                    = 2
     Object ID                        = 3
DB21011I  In a partitioned database server environment, only the table spaces
on the current node are listed. 

 

Please keep the Note on status on the tablespace:

State                                = 0x0000

 

 

·            0x0          Normal
·            0x1          Quiesced: SHARE
·            0x2          Quiesced: UPDATE
·            0x4          Quiesced: EXCLUSIVE
·            0x8          Load pending
·            0x10         Delete pending
·            0x20         Backup pending
·            0x40         Roll forward in progress
·            0x80         Roll forward pending
·            0x100        Restore pending
·            0x100        Recovery pending (not used)
·            0x200        Disable pending
·            0x400        Reorg in progress
·            0x800        Backup in progress
·            0x1000       Storage must be defined
·            0x2000       Restore in progress
·            0x4000       Offline and not accessible
·            0x8000       Drop pending
·            0x2000000    Storage may be defined
·            0x4000000    StorDef is in 'final' state
·            0x8000000    StorDef was changed prior to rollforward
·            0x10000000   DMS rebalancer is active
·            0x20000000   TBS deletion in progress
·            0x40000000   TBS creation in progress
·            0x8          For service use only 

db2tbst - Get tablespace state.

Authorization - none , Required connection none, syntax db2tbst tabpespace-state: The state value is part of the output of list tablespaces example

db2tbst 0X0000 returns state normal

Db2 Logs:

Find the db2diag.log file from the sample database

Path:/home/smith…./sqllib/db2dump directory

Cat the db2daig.log to see the logs and if the file is more than 5 or 6 10 MB to GB. you can remove that db2diag.log file from this path my moving or deleting it .The Db will create the new file automatically with 0 size and start logging.

We also increase the size of the log/more details in the log file by

db2 update dbm cfg using diaglevel 4

Database creation and privileges:

db2 => create database sample

DB20000I The CREATE DATABASE command completed successfully.

Now I want to revoke connect, createtab bindadd on database from public

user Privileges:

On server: db2 => revoke connect , createtab, bindadd on database from public

Now on client, as user1, I tried to connect to sample

db2 => connect to sample

SQL1060N User "user1" does not have the CONNECT privilege. SQLSTATE=08004

Group Privileges:

db2 => grant connect, createtab on database to group grp1

DB20000I The SQL command completed successfully.

Tested on client I can connect successfully.

Now on the client, I can connect as a student, list tables but not select. I

can still describe tables

To prevent this:

On server

revoke select on table syscat.columns from public

Now on client, I cannot describe but also on my tables.

db2 => revoke select on table syscat.columns from public

DB20000I The SQL command completed successfully.

db2 => grant select on table syscat.columns to group grp1

On server:

db2 => revoke select on table syscat.indexes from public

DB20000I The SQL command completed successfully.

select * from syscat.dbauth will display all the privileges for

dbadm authority:

DBADMAUTH CREATETABAUTH BINDADDAUTH CONNECTAUTH

NOFENCEAUTH IMPLSCHEMAAUTH LOAD AUTH

select

TABNAME,DELETEAUTH,INSERTAUTH,SELECTAUTH from

syscat.tabauth

grant connect, createtab

grant connect, createtab on database to user usr1

to group grp1

 Database backup

1. db2ckbkp - Check Backup Command

db2ckbkp SAMPLE.0.krodger.NODE0000.CATN0000.19990817150714.001

This is to check the db backup file is good or not

http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=...

2. db2 backup database sample to /dev3/backup

This is to take the database backup to file system

http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=...

3. db2 backup database sample to c:\backup

Backup the database

db2 restore database sample from c:\backup

Restore the database.

http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=...

 

 

 

 

Views: 1497

Comment

You need to be a member of ChannelDB2 to add comments!

Join ChannelDB2

Featured Downloads

Try BLU Acceleration on Cloud

© 2019   Created by channeldb2.   Powered by

Badges  |  Report an Issue  |  Terms of Service