Prev: knowing if an NLI is active or has occurred recently
Next: DB2 UDB Security Best Practices documentation
From: Bruce on 20 Apr 2010 10:08
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
From: Mark A on 20 Apr 2010 13:33
"Bruce" <bwmiller16(a)gmail.com> wrote in message
news:ae7155e6-6ce7-49b0-9ccb-f4a5f441748e(a)22g2000vbg.googlegroups.com...
> 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

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


From: Bruce on 20 Apr 2010 15:21
On Apr 20, 1:33 pm, "Mark A" <no...(a)nowhere.com> wrote:
> "Bruce" <bwmille...(a)gmail.com> wrote in message
>
> news:ae7155e6-6ce7-49b0-9ccb-f4a5f441748e(a)22g2000vbg.googlegroups.com...
>
> > 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
>
> Did you try db2look? You can pull DDL for one table with -t option

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.
From: The Boss on 20 Apr 2010 17:55
Bruce wrote:
> On Apr 20, 1:33 pm, "Mark A" <no...(a)nowhere.com> wrote:
>> "Bruce" <bwmille...(a)gmail.com> wrote in message
>>
>> news:ae7155e6-6ce7-49b0-9ccb-f4a5f441748e(a)22g2000vbg.googlegroups.com...
>>
>>> 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
>>
>> Did you try db2look? You can pull DDL for one table with -t option
>
> 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.

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/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.ha.doc/doc/c0006079.html

HTH

--
Jeroen


From: Naresh Chainani on 22 Apr 2010 11:25
On Apr 22, 5:35 am, Bruce <bwmille...(a)gmail.com> wrote:
> On Apr 21, 7:47 pm, "Mark A" <no...(a)nowhere.com> wrote:
>
> > "The Boss" <use...(a)No.Spam.Please.invalid> wrote in message
>
> >news:4bcf7ed0$0$4570$e4fe514c(a)dreader30.news.xs4all.nl...
>
> > > 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
>
> > Bruce understands that one should not use it an HADR environment, but he
> > needs to check to see if someone else did it.
>
> 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?".

Among other places, DB2 stores table information in packed
descriptors. The NLI-attribute of a table is present there and the
packed descriptor can be dumped using db2cat. I understand you are
looking for something that you could query, but I am not aware of any.

db2 "CREATE TABLE PARTTABNLI (ID INT, ALPHA VARCHAR(20)) PARTITION BY
RANGE (ID) (STARTING FROM (1) ENDING AT (150) EVERY(10) , STARTING
FROM (200) ENDING AT (250) EVERY (10)) NOT LOGGED INITIALLY"

db2cat -db eee -n PARTTABNLI -s NARESH -t | grep "Table not logged"
Table not logged : 1

The 'Table not logged' field is set to 0, if NLI attribute is not
specified during table creation.

Perhaps this could serve as your alternative mechanism to validate
whether any table is created as NLI. You should be able to automate
this by querying the database for all tables of interest and invoking
db2cat.

Naresh
 |  Next  |  Last
Pages: 1 2
Prev: knowing if an NLI is active or has occurred recently
Next: DB2 UDB Security Best Practices documentation