IBM Support

Finding if Table created as NOT LOGGED INITIALLY

Technote (FAQ)


Question

How to find if Table was Created as NOT LOGGED INITIALLY?

Answer

We can use either of the following methods to Check if Table was created as 'Not Logged Initially'
1)Using db2cat,

db2cat -db <dbname> -n <TableName> -s <Schema> -t | grep "Table not logged"
Which can confirm if given table was created as NLI or not.

Sample Output
----------------------
Connecting to the Database.
Connect to Database Successful.
Table not logged : 0
----------------------
Table not Logged showing '0' represent Table was not created as NLI, if it returns '1' then that is table was created as NLI.

2)Using db2look
Using db2look command to get DDL information on the table which would show you if
the table was created as "NOT LOGGED INITIALLY"

Example:
db2look -d <database_name> -t <table_name> -e -o <output_file_name.out>

NOTE: Table Alter with ACTIVATE NOT LOGGED INITIALLY will not be listed in these above commands as it will activate NLI only for current Unit of work.

NOTE:In DB2 V8 the LOG_ATTRIBUTE column in the SYSCAT.TABLES table was used to
check if a table was created as NOT LOGGED INITIALLY.
But Starting from DB2 V9 this column is no longer being used and will always have a value of 0.

Related information

NOT LOGGED INITIALLY

Document information

More support for: DB2 for Linux, UNIX and Windows
Database Objects/Config - Tables

Software version: 9.1, 9.5, 9.7, 10.1, 10.5

Operating system(s): AIX, HP-UX, Linux, Solaris, Windows

Reference #: 1984463

Modified date: 07 June 2016