Hi all -

Running AIX 6, UDB 9.1.4.

Other than referring to the db2inst1.nfy log is there anyway to know
if a table has been created with 'not logged initially'? I need
something that I can query to determine the NLI status.

Thanks,

Bruce

"Bruce" <bwmiller16 (AT) gmail (DOT) com> wrote

Did you try db2look? You can pull DDL for one table with -t option

On Apr 20, 1:33*pm, "Mark A" <no... (AT) nowhere (DOT) com> wrote:
That's an idea...but is it going to tell me what's been NLI-activated
in the last...say...2 days? I need to know when and if I miss
the .nfy msg and if my HADR standby is still good.

On Apr 20, 1:33*pm, "Mark A" <no... (AT) nowhere (DOT) com> wrote:
I need something 'queryable'...where does DB2 store the info re: the
NLI? its one thing to create a TABLE with NLI but another thing
entirely to know that its done the load and that the NLI option is,
essentially, complete. I need to know that my HADR standby is trust-
worthy.

Bruce wrote:
It won't be.
From the docs:

<q>
Because changes to the table are not logged, you should consider the
following when deciding to use the NOT LOGGED INITIALLY table attribute:

- All changes to the table will be flushed out to disk at commit time. This
means that the commit might take longer.

- If the NOT LOGGED INITIALLY attribute is activated and an activity occurs
that is not logged, the entire unit of work will be rolled back if a
statement fails or a ROLLBACK TO SAVEPOINT is executed (SQL1476N).

- If you are using high availability disaster recovery (HADR) you should not
use the NOT LOGGED INITIALLY table attribute. Tables created on the primary
database with the NOT LOGGED INITIALLY option specified are not replicated
to the standby database. Attempts to access such tables on an active standby
database or after the standby becomes the primary as a result of a takeover
operation will result in an error (SQL1477N).

- You cannot recover these tables when rolling forward. If the rollforward
operation encounters a table that was created or altered with the NOT LOGGED
INITIALLY option, the table is marked as unavailable. After the database is
recovered, any attempt to access the table returns SQL1477N.
</q>
Source:
http://publib.boulder.ibm.com/infoce.../c0006079.html

HTH

--
Jeroen

On 4/20/10 12:21 PM, Bruce wrote:
It used to be that unless you specified "NOT LOGGED INITIALLY" when you
created a table you could not use "ALTER TABLE ... NOT LOGGED
INITIALLY." However, this changed a LONG time ago -- perhaps in V7.1,
maybe earlier -- such that you no longer have allow this for a table
when you create the table.

Did you try SYSIBMADM.PDLOGMSGS_LAST24HOURS ? As in:

select timestamp, msg
from sysibmadm.pdlogmsgs_last24hours
where msgnum = 5530;


This (of course) only shows you events from the last 24 hours, so if
you want to see older you would have to look at db2diag.log. You can
do this quite easily using db2diag:

db2diag -H 36h -g msg:=ADM5530W

On Apr 21, 12:38*am, Ian <ianb... (AT) mobileaudio (DOT) com> wrote:
Thank You. Yes this is more towards what I need...but it appears that
SYSIBMADM.PDLOGMSGS_LAST24HOURS simply goes out to the .nfy log and
simply dumps what's already there...I'm already scanning my db2diag
and .nfy logs for this NLI info but it means I can do a SELECT to get
the data instead of hassling with GREP. Again, I'm looking for a
short list of those tables that may be inconsistent or marked 'bad' on
the HADR standby-side to determine which tables need to be rebuilt due
to an NLI. I'm simply not seeing what I need.

Where does db2 store the info on these NLI tables on the standby-side?

Bruce wrote:
It doesn't, and you shouldn't use NLI in a HADR environment.
Please read my previous reply I posted about 24 hours ago.

--
Jeroen

"The Boss" <usenet (AT) No (DOT) Spam.Please.invalid> wrote

Bruce understands that one should not use it an HADR environment, but he
needs to check to see if someone else did it.

On Apr 21, 7:47*pm, "Mark A" <no... (AT) nowhere (DOT) com> wrote:
Right...HADR has literally saved our B**T many times and we absolutely
positively need it...Do I wish that PeopleSoft didn't use NLI? Sure,
but it does and there isn't a way around it. So, I'm stuck with what
I've been given. Now, back to the question: "How can I know from
looking at something in DB2 if HADR has been compromised from an NLI-
standpoint?".