Search

Ramchandra's Weblog

Just ……everything about DB2 LUW

Month

August 2008

NFS in AIX

DB2 Database for Linux, UNIX, and Windows

Advertisements

Configuration of AIX Fast Connect and SMBFS

Configuration of AIX Fast Connect and SMBFS

The Common Internet File System (CIFS), also known as Server Message Block (SMB),
is a standard remote file system access protocol over the Internet, enabling
groups of users to work together and share documents and printers across the
Internet or within corporate intranets. CIFS allows multiple clients to access and
update the same file, while preventing conflicts with sophisticated file sharing
and locking semantics. It also permits aggressive caching and read-ahead and
write-behind without loss of cache coherency, thereby increasing the performance,
which is the backbone of today’s sophisticated enterprise computer networks. CIFS
complements HTTP and provides more sophisticated file sharing and file transfer
than older protocols, such as FTP.

File sharing between PC operating systems, such as Windows®, is commonly
implemented using the CIFS protocol, and file sharing between AIX® systems has
been implemented using the Network File System (NFS) protocol. Since these two
protocols being non-interoperable, products like AIX Fast Connect and AIX SMBFS
allow PC clients to access and share files on the AIX server and vice versa.

How to determine if an index needs to be rebuilt

IBM – How to determine if an index needs to be rebuilt

How to determine if an index needs to be rebuilt

Question
This document provides information about how to determine if an index
needs rebuilding and how to monitor index rebuilds to know if they have
completed.
Answer
You can check if an index needs to be rebuilt by one of the following means:

  • Using the inspect database tool. For example:
    1. Issue the command:
      db2 “inspect check database for error state all results inspect.out”

    2. Then cd to the DIAGPATH.
      For example on UNIX®, this would be: cd $INSTHOME/sqllib/db2dump

    3. Issue the command:
      db2inspf inspect.out inspect.fmt

    4. If you view the corresponding inspect.fmt file, a message similar to the following will appear:
      Index phase start. Object: 3 Tablespace: 2
      Warning: Object is known to be in error state x400.
      Warning: Index object is in invalid state, requires index be re-built.

  • Using the database analysis and reporting tool (db2dart). For example:
    1. If you have a specific table to query, issue the following command:
      select tableid,tbspaceid,tabname,tabschema from syscat.tables where tabname = ‘<name of your table>’

    2. Then run the db2dart tool:
      db2dart <dbname> /T /TSI <tbspaceid from above> /OI <tableid from above>

    3. View the <dbname>.RPT (found in your DIAGPATH). Look for a message similar to the following:
      Index phase start. Object: 3 Tablespace: 2
      Warning: Object is known to be in error state x400.
      Warning: Index object is in invalid state, requires index be re-built.

You can determine whether the index rebuild has completed using one of following two methods:

  • Parse the db2diag.log using the db2diag utility for the function sqlrlRecreateIndex or sqldIndexCreate.
    Here’s an example of a db2diag.log message related to rebuild:

    2005-12-06-11.38.50.559186-300 E179511A430 LEVEL: Warning
    PID : 22917368 TID : 1 PROC : db2agent (SAMPLE)
    INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
    APPHDL : 0-758 APPID: *...
    FUNCTION: DB2 UDB, data management, sqldBeginIndexCreate, probe:1
    MESSAGE : ADM5540W Beginning index rebuild of "1" indexes on table "DB2INST1.STAFF".

    2005-12-06-11.38.50.562902-300 E179942A405 LEVEL: Warning
    PID : 22917368 TID : 1 PROC : db2agent (SAMPLE)
    INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
    APPHDL : 0-758 APPID: *...
    FUNCTION: DB2 UDB, data management, sqldIndexCreate, probe:1
    MESSAGE : ADM5541W Rebuilding index with IID "1" on table "DB2INST1.STAFF".

    2005-12-06-11.38.50.686043-300 E180348A437 LEVEL: Warning
    PID : 22917368 TID : 1 PROC : db2agent (SAMPLE)
    INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
    APPHDL : 0-758 APPID: *...
    FUNCTION: DB2 UDB, data management, sqldEndIndexCreate, probe:1
    MESSAGE : ADM5542W Index rebuild of indexes on table "DB2INST1.STAFF" completed successfully.

  • Parse the instance notification log (for example, named <instance name>.nfy on UNIX) for index rebuilds.
    The messages will be similar to the following:

    2005-12-06-11.38.50.558642 Instance:db2inst1 Node:000
    PID:22917368(db2agent (SAMPLE)) TID:1 Appid:*...
    data management sqldBeginIndexCreate Probe:1 Database:SAMPLE
    ADM5540W Beginning index rebuild of "1" indexes on table "DB2INST1.STAFF".

    2005-12-06-11.38.50.562590 Instance:db2inst1 Node:000
    PID:22917368(db2agent (SAMPLE)) TID:1 Appid:*...
    data management sqldIndexCreate Probe:1 Database:SAMPLE
    ADM5541W Rebuilding index with IID "1" on table "DB2INST1.STAFF".

    2005-12-06-11.38.50.685618 Instance:db2inst1 Node:000
    PID:22917368(db2agent (SAMPLE)) TID:1 Appid:*...
    data management sqldEndIndexCreate Probe:1 Database:SAMPLE
    ADM5542W Index rebuild of indexes on table "DB2INST1.STAFF" completed successfully.

Here
is an example of how you might perform both the initial check (to
determine if the rebuild should be performed) and then the actual
monitoring of the rebuild:

  1. Issue the commands:
    db2 “export to staff.ixf of ixf select * from staff”
    db2 “create index id1 on staff (id)”
    db2 “load from staff.ixf of IXF replace into staff indexing mode deferred”

  2. Change to the $DIAGPATH directory (for example $INSTHOME/sqllib/db2dump on UNIX).
  3. Issue the commands:
    db2 “inspect check database for error state all results inspect.raw”
    db2inspf inspect.raw inspect.fmt

  4. Analyze the inspect.fmt file
  5. Issue the commands:
    db2 terminate
    db2 restart db sample

  6. View the db2diag.log and instance notification log for messages.

Automatic table maintenance in DB2

Automatic table maintenance in DB2, Part 1: Automatic statistics collection in DB2 for Linux, UNIX, and Windows

Java application deployment considerations – db2 drivers

Java application deployment considerations

java application deployment considerations

Java application deployment considerations – db2 drivers

Java application deployment considerations

ava application deployment considerations

Advanced Perl with DB2 UDB

Advanced Perl with DB2 UDB

db2 with perl

Perl programming with DB2 Universal Database

Explain Plan of DB2 Stored Procedures /static sql

How to create Explain Plan of DB2 Stored Procedures? | db2ude

Explain Plan of DB2 Stored Procedures?

Blog at WordPress.com.

Up ↑