mini FAQ DB2

Creation: 6 Aout 2006
Mise à jour: 28 Octobre 2010
Version: 1.0
Author: Jean-Louis Bicquelet-Salaün
Location: http://jlbicquelet.free.fr
Copyright: (c) 2006-2010 Jean-Louis BICQUELET-SALAÜN
DB2 Frequently-Asked Questions

DB2 Frequently-Asked Questions

Pour plus d'information au sujet de cette faq, contactez Jean-Louis BICQUELET


FAQ Revised: Thursday 28 October 2010 15:00:55


Table of Contents

1. instances
2. autorisations
3. applications
4. installation
5. base
6. information
7. utilisation
8. date et heure
9. incidents

1. instances

1.1. Comment connaitre l'heure de démarrage d'une instance db2 ?
db2 get dbm cfg

          Database Manager Configuration

     Node type = Enterprise Server Edition with local and remote clients

 Database manager configuration release level            = 0x0a00

 CPU speed (millisec/instruction)             (CPUSPEED) = 4.762804e-07
 Communications bandwidth (MB/sec)      (COMM_BANDWIDTH) = 1.000000e+02

 Max number of concurrently active databases     (NUMDB) = 2
 Data Links support                          (DATALINKS) = NO
 Federated Database System Support           (FEDERATED) = NO
 Transaction processor monitor name        (TP_MON_NAME) =

 Default charge-back account           (DFT_ACCOUNT_STR) =

 Java Development Kit installation path       (JDK_PATH) = /usr/java14_64

 Diagnostic error capture level              (DIAGLEVEL) = 3
 Notify Level                              (NOTIFYLEVEL) = 3
 Diagnostic data directory path               (DIAGPATH) = /p/uipsid01/db2dump

 Default database monitor switches
   Buffer pool                         (DFT_MON_BUFPOOL) = ON
   Lock                                   (DFT_MON_LOCK) = ON
   Sort                                   (DFT_MON_SORT) = ON
   Statement                              (DFT_MON_STMT) = ON
   Table                                 (DFT_MON_TABLE) = ON
   Timestamp                         (DFT_MON_TIMESTAMP) = ON
   Unit of work                            (DFT_MON_UOW) = ON
 Monitor health of instance and databases   (HEALTH_MON) = OFF

 SYSADM group name                        (SYSADM_GROUP) = GIPSID01
 SYSCTRL group name                      (SYSCTRL_GROUP) = GMPSID01
 SYSMAINT group name                    (SYSMAINT_GROUP) =
 SYSMON group name                        (SYSMON_GROUP) =

 Database manager authentication        (AUTHENTICATION) = SERVER_ENCRYPT
 Cataloging allowed without authority   (CATALOG_NOAUTH) = NO
 Trust all clients                      (TRUST_ALLCLNTS) = YES
 Trusted client authentication          (TRUST_CLNTAUTH) = CLIENT
 Bypass federated authentication            (FED_NOAUTH) = NO

 Default database path                       (DFTDBPATH) = /prd/home/uipsid01

 Database monitor heap size (4KB)          (MON_HEAP_SZ) = 4096
 Java Virtual Machine heap size (4KB)     (JAVA_HEAP_SZ) = 2048
 Audit buffer size (4KB)                  (AUDIT_BUF_SZ) = 0
 Size of instance shared memory (4KB)  (INSTANCE_MEMORY) = AUTOMATIC
 Backup buffer default size (4KB)            (BACKBUFSZ) = 1024
 Restore buffer default size (4KB)           (RESTBUFSZ) = 1024

 Sort heap threshold (4KB)                  (SHEAPTHRES) = 375000

 Directory cache support                     (DIR_CACHE) = YES

 Application support layer heap size (4KB)   (ASLHEAPSZ) = 15
 Max requester I/O block size (bytes)         (RQRIOBLK) = 32767
 Query heap size (4KB)                   (QUERY_HEAP_SZ) = 10000
 DRDA services heap size (4KB)            (DRDA_HEAP_SZ) = 128

 Workload impact by throttled utilities(UTIL_IMPACT_LIM) = 100

 Priority of agents                           (AGENTPRI) = SYSTEM
 Max number of existing agents               (MAXAGENTS) = 400
 Agent pool size                        (NUM_POOLAGENTS) = 200(calculated)
 Initial number of agents in pool       (NUM_INITAGENTS) = 0
 Max number of coordinating agents     (MAX_COORDAGENTS) = (MAXAGENTS - NUM_INITAGENTS)
 Max no. of concurrent coordinating agents  (MAXCAGENTS) = MAX_COORDAGENTS
 Max number of client connections      (MAX_CONNECTIONS) = MAX_COORDAGENTS

 Keep fenced process                        (KEEPFENCED) = YES
 Number of pooled fenced processes         (FENCED_POOL) = MAX_COORDAGENTS
 Initial number of fenced processes     (NUM_INITFENCED) = 0

 Index re-creation time                       (INDEXREC) = RESTART

 Transaction manager database name         (TM_DATABASE) = 1ST_CONN
 Transaction resync interval (sec)     (RESYNC_INTERVAL) = 180

 SPM name                                     (SPM_NAME) =
 SPM log size                          (SPM_LOG_FILE_SZ) = 256
 SPM resync agent limit                 (SPM_MAX_RESYNC) = 20
 SPM log path                             (SPM_LOG_PATH) =

 TCP/IP Service name                          (SVCENAME) = 50001
 Discovery mode                               (DISCOVER) = DISABLE
 Discover server instance                (DISCOVER_INST) = DISABLE

 Maximum query degree of parallelism   (MAX_QUERYDEGREE) = ANY
 Enable intra-partition parallelism     (INTRA_PARALLEL) = NO

 No. of int. communication buffers(4KB)(FCM_NUM_BUFFERS) = 25600
 Number of FCM request blocks              (FCM_NUM_RQB) = AUTOMATIC
 Number of FCM connection entries      (FCM_NUM_CONNECT) = AUTOMATIC
 Number of FCM message anchors         (FCM_NUM_ANCHORS) = AUTOMATIC

 Node connection elapse time (sec)         (CONN_ELAPSE) = 10
 Max number of node connection retries (MAX_CONNRETRIES) = 5
 Max time difference between nodes (min) (MAX_TIME_DIFF) = 60

 db2start/db2stop timeout (min)        (START_STOP_TIME) = 10

1.2. Comment se connecter à une base ?
db2 => connect to password Informations de connexion à la base de données Serveur de base de données = DB2/NT 8.1.0 ID utilisateur SQL = JLBICQUE... Alias local de la base de données = PASSWORD db2 => connect to password SQL0752N La connexion à une base de données n'est pas autorisée dans une unité d'oeuvre logique lorsqu'un paramètre CONNECT de type 1 est en cours d'utilisation. SQLSTATE=0A001

1.3. Comment connaitre les instances existantes ?
db2ilist
DB2CTLSV
DB2



2. autorisations

2.1. Comment se connecter à une base ?
db2 => connect to password Informations de connexion à la base de données Serveur de base de données = DB2/NT 8.1.0 ID utilisateur SQL = JLBICQUE... Alias local de la base de données = PASSWORD db2 => connect to password SQL0752N La connexion à une base de données n'est pas autorisée dans une unité d'oeuvre logique lorsqu'un paramètre CONNECT de type 1 est en cours d'utilisation. SQLSTATE=0A001


3. applications

3.1. Comment lister les applications ?
db2 list applications

ID aut.  Nom de         Descript.      ID application                 Nom      N
bre
         l'application  appl.                                     BDD     agents

-------- -------------- ---------- ------------------------------ -------- -----

JLBICQUE>javaw.exe      52         *LOCAL.DB2.00B0C3092946        PASSWORD 1

JLBICQUE>javaw.exe      37         *LOCAL.DB2.00B283092341        PASSWORD 1

JLBICQUE>db2bp.exe      58         *LOCAL.DB2.014C03102818        SAMPLE   1

JLBICQUE>javaw.exe      54         *LOCAL.DB2.00D703093305        SAMPLE   1

JLBICQUE>db2bp.exe      53         *LOCAL.DB2.016483093304        SAMPLE   1

JLBICQUE>javaw.exe      50         *LOCAL.DB2.009983092903        SAMPLE   1

JLBICQUE>javaw.exe      33         *LOCAL.DB2.004243091455        SAMPLE   1

JLBICQUE>javaw.exe      31         *LOCAL.DB2.00AB03091343        SAMPLE   1

DB2ADMIN db2dasstm.exe  48         *LOCAL.DB2.0178C3092529        DWCTRLDB 1

DB2ADMIN iwh2serv.exe   25         *LOCAL.DB2.011D43090837        DWCTRLDB 1

DB2ADMIN iwh2log.exe    24         *LOCAL.DB2.016BC3090835        DWCTRLDB 1

DB2ADMIN db2dasstm.exe  16         *LOCAL.DB2.018943090735        DWCTRLDB 1

3.2. Comment lister les applications avec plus de détail?
db2 list applications show detail


4. installation

4.1. Comment vérifier le niveau de db2?
#db2level

DB21085I  Instance "dbinst" uses "32" bits and DB2 code release "SQL08020"
with level identifier "03010106".
Informational tokens are "DB2 v8.1.1.72", "s040914", "U498350", and FixPak "7".
Product is installed at "/usr/opt/db2_08_01".




5. base

5.1. comment lister les bases disponibles ?
db2 list db directory

 Répertoire système des bases de données

 Nombre d'entrées dans le répertoire = 3

Entrée de la base de données               1 :

 Alias de la base de données               = PASSWORD
 Nom de la base de données           = PASSWORD
 Unité de la base de données               = C:\DB2
 Niveau d'édition de la base de données                       = a.00
 Commentaire                      = test et memo password
 Type d'entrée du répertoire               = Indirect
 Num. de partition de base de données du catalogue = 0

Entrée de la base de données               2 :

 Alias de la base de données               = DWCTRLDB
 Nom de la base de données           = DWCTRLDB
 Unité de la base de données               = C:\DB2
 Niveau d'édition de la base de données                       = a.00
 Commentaire                      =
 Type d'entrée du répertoire               = Indirect
 Num. de partition de base de données du catalogue = 0

Entrée de la base de données               3 :

 Alias de la base de données               = SAMPLE
 Nom de la base de données           = SAMPLE
 Unité de la base de données               = C:\DB2
 Niveau d'édition de la base de données                       = a.00
 Commentaire                      =
 Type d'entrée du répertoire               = Indirect
 Num. de partition de base de données du catalogue = 0



6. information

6.1. Comment connaitre l'heure de démarrage d'une instance db2
db2 get dbm cfg

          Database Manager Configuration

     Node type = Enterprise Server Edition with local and remote clients

 Database manager configuration release level            = 0x0a00

 CPU speed (millisec/instruction)             (CPUSPEED) = 4.762804e-07
 Communications bandwidth (MB/sec)      (COMM_BANDWIDTH) = 1.000000e+02

 Max number of concurrently active databases     (NUMDB) = 2
 Data Links support                          (DATALINKS) = NO
 Federated Database System Support           (FEDERATED) = NO
 Transaction processor monitor name        (TP_MON_NAME) =

 Default charge-back account           (DFT_ACCOUNT_STR) =

 Java Development Kit installation path       (JDK_PATH) = /usr/java14_64

 Diagnostic error capture level              (DIAGLEVEL) = 3
 Notify Level                              (NOTIFYLEVEL) = 3
 Diagnostic data directory path               (DIAGPATH) = /p/uipsid01/db2dump

 Default database monitor switches
   Buffer pool                         (DFT_MON_BUFPOOL) = ON
   Lock                                   (DFT_MON_LOCK) = ON
   Sort                                   (DFT_MON_SORT) = ON
   Statement                              (DFT_MON_STMT) = ON
   Table                                 (DFT_MON_TABLE) = ON
   Timestamp                         (DFT_MON_TIMESTAMP) = ON
   Unit of work                            (DFT_MON_UOW) = ON
 Monitor health of instance and databases   (HEALTH_MON) = OFF

 SYSADM group name                        (SYSADM_GROUP) = GIPSID01
 SYSCTRL group name                      (SYSCTRL_GROUP) = GMPSID01
 SYSMAINT group name                    (SYSMAINT_GROUP) =
 SYSMON group name                        (SYSMON_GROUP) =

 Database manager authentication        (AUTHENTICATION) = SERVER_ENCRYPT
 Cataloging allowed without authority   (CATALOG_NOAUTH) = NO
 Trust all clients                      (TRUST_ALLCLNTS) = YES
 Trusted client authentication          (TRUST_CLNTAUTH) = CLIENT
 Bypass federated authentication            (FED_NOAUTH) = NO

 Default database path                       (DFTDBPATH) = /prd/home/uipsid01

 Database monitor heap size (4KB)          (MON_HEAP_SZ) = 4096
 Java Virtual Machine heap size (4KB)     (JAVA_HEAP_SZ) = 2048
 Audit buffer size (4KB)                  (AUDIT_BUF_SZ) = 0
 Size of instance shared memory (4KB)  (INSTANCE_MEMORY) = AUTOMATIC
 Backup buffer default size (4KB)            (BACKBUFSZ) = 1024
 Restore buffer default size (4KB)           (RESTBUFSZ) = 1024

 Sort heap threshold (4KB)                  (SHEAPTHRES) = 375000

 Directory cache support                     (DIR_CACHE) = YES

 Application support layer heap size (4KB)   (ASLHEAPSZ) = 15
 Max requester I/O block size (bytes)         (RQRIOBLK) = 32767
 Query heap size (4KB)                   (QUERY_HEAP_SZ) = 10000
 DRDA services heap size (4KB)            (DRDA_HEAP_SZ) = 128

 Workload impact by throttled utilities(UTIL_IMPACT_LIM) = 100

 Priority of agents                           (AGENTPRI) = SYSTEM
 Max number of existing agents               (MAXAGENTS) = 400
 Agent pool size                        (NUM_POOLAGENTS) = 200(calculated)
 Initial number of agents in pool       (NUM_INITAGENTS) = 0
 Max number of coordinating agents     (MAX_COORDAGENTS) = (MAXAGENTS - NUM_INITAGENTS)
 Max no. of concurrent coordinating agents  (MAXCAGENTS) = MAX_COORDAGENTS
 Max number of client connections      (MAX_CONNECTIONS) = MAX_COORDAGENTS

 Keep fenced process                        (KEEPFENCED) = YES
 Number of pooled fenced processes         (FENCED_POOL) = MAX_COORDAGENTS
 Initial number of fenced processes     (NUM_INITFENCED) = 0

 Index re-creation time                       (INDEXREC) = RESTART

 Transaction manager database name         (TM_DATABASE) = 1ST_CONN
 Transaction resync interval (sec)     (RESYNC_INTERVAL) = 180

 SPM name                                     (SPM_NAME) =
 SPM log size                          (SPM_LOG_FILE_SZ) = 256
 SPM resync agent limit                 (SPM_MAX_RESYNC) = 20
 SPM log path                             (SPM_LOG_PATH) =

 TCP/IP Service name                          (SVCENAME) = 50001
 Discovery mode                               (DISCOVER) = DISABLE
 Discover server instance                (DISCOVER_INST) = DISABLE

 Maximum query degree of parallelism   (MAX_QUERYDEGREE) = ANY
 Enable intra-partition parallelism     (INTRA_PARALLEL) = NO

 No. of int. communication buffers(4KB)(FCM_NUM_BUFFERS) = 25600
 Number of FCM request blocks              (FCM_NUM_RQB) = AUTOMATIC
 Number of FCM connection entries      (FCM_NUM_CONNECT) = AUTOMATIC
 Number of FCM message anchors         (FCM_NUM_ANCHORS) = AUTOMATIC

 Node connection elapse time (sec)         (CONN_ELAPSE) = 10
 Max number of node connection retries (MAX_CONNRETRIES) = 5
 Max time difference between nodes (min) (MAX_TIME_DIFF) = 60

 db2start/db2stop timeout (min)        (START_STOP_TIME) = 10



7. utilisation

7.1. comment connaitre les tables d'une base ?
db2 "select NAME from sysibm.systables "

7.2. comment selectionner une partie d'une table
db2 "select NAME from sysibm.systables " | grep 20061009

7.3. commen supprimer une table ?
db2 drop table PSAPPELMADEMISSION20061009T000000EB


8. date et heure

8.1. comment obtenir la date dans db2 ?
db2 SELECT current date FROM sysibm.sysdummy1

1
----------
2010-10-28

  1 record(s) selected.

Une autre manière de faire:

values DATE(current timestamp)

1
----------
2010-10-28

  1 record(s) selected.

Et pour terminer

  db2 values current date


8.2. comment obtenir l'heure dans db2 ?
SELECT current time FROM sysibm.sysdummy1


1
--------
11.06.19

ou

SELECT current timestamp FROM sysibm.sysdummy1


1
--------------------------
2010-10-28-11.06.44.140257

  1 record(s) selected.


8.3. comment faire des opérations sur les dates ?
VALUES current date +1 year

1
----------
2011-10-28


8.4. comment connaitre le nombre de jour depuis sa naissance ?
values days (current date) - days (date('1965-01-10'))

1
-----------
      16727

  1 record(s) selected.


8.5. comment connaitre le numéro de la semaine en cours?
 values week (current timestamp)

1
-----------
         44

  1 record(s) selected.


8.6. comment tenir compte du timezone?
values current timestamp - current timezone


8.7. comment déterminer la valeur du timezone?
values current timezone

1
--------
  10000,

  1 record(s) selected.


8.8. comment afficher le temps local et le temps utc en même temps?
   with t (local_time, utc_time) as ( values (current_timestamp, current_timestamp - current timezone) ) select * from t

LOCAL_TIME                 UTC_TIME
-------------------------- --------------------------
2010-10-28-12.08.16.937606 2010-10-28-11.08.16.937606

  1 record(s) selected.



9. incidents

9.1. Que faire quand on obtient un message SQL1072C ?
Pour un message du type :
db2start
05/24/2007 07:23:13     0   0   SQL1072C  The database manager resources are in an inconsistent state.  DB2 may have been incorrectly terminated, or another application may be using system resources in a way which conflicts with DB2's use of system resources.  System resource cleanup may be required.
SQL1032N  No start database manager command was issued.  SQLSTATE=57019
 Niveau d'édition de la base de données                       = a.00
 Commentaire                      =
 Type d'entrée du répertoire               = Indirect
 Num. de partition de base de données du catalogue = 0

Utilisez ipcs et ipcrm pour supprimer les queues, memory, etc de la share memory et relancez db2start.


Copyright (c) 2006 -2007Jean-Louis BICQUELET

This list of questions and answers was generated by makefaq.