level0 required daily
A user on IDS 9.40.FC9 suddenly found the server demanding a level-0 archive every day ('next archive for rootdbs will be level-0', 'too old' errors when attempting L1), where previously L1 daily plus L0 at weekends sufficed; he wondered if heavy use of temp tables on huge tables was to blame. Respondents explained the flag means those dbspaces have had so much update activity that IDS forbids L1/L2 and forces an L0, and offered a sysmaster query (bitval flags '0x20000') plus onstat -d / oncheck -pr to check it. A known bug prematurely setting the flag in some versions was mentioned, and upgrading from the out-of-support 9.40 was advised, but no definitive cause or fix was confirmed in the thread.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
hi to all IDS (version 9.40.FC9) daily needs a L0 archive, i saw some topics on iiug and on the ibm site, and i haven't found the reason. i've just noticed that there's too many sql statements that uses temporary tables (select ..into temp ...) on large tables (more than 2.000.000 records and even one table with more than 100 millions records) could this (temp tables) cause the need of L0, daily ?? thank(s in advance
Assume your L0 archive means Level 0 backup. Your database Level 0 backup job is not required by IDS, it should be a daily backup strategy created by your business requirement. SQL, Table type or manipulation should have nothing to do with daily backup. Frank On Tue, Sep 21, 2010 at 3:09 PM, SMITH JOHN <daylight@webmails.com> wrote: > hi to all > > IDS (version 9.40.FC9) daily needs a L0 archive, i saw some topics on iiug > and > on the ibm site, and i haven't found the reason. > > i've just noticed that there's too many sql statements that uses temporary > tables (select ..into temp ...) on large tables (more than 2.000.000 > records > and even one table with more than 100 millions records) > > could this (temp tables) cause the need of L0, daily ?? > > thank(s in advance > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016364d29935496140490cb7737
thank's a lot for your answer, how can i know if it's Ids that's requires this L0 or not ? i forgot to tell that's when i want to do an L1 archive it prompts that the version of rootdbs is too old... for exemple L0 yesterday at 6 pm some batchs L1 at 8pm today at 12 am i see, in the ids message log : "next backup of rootdbs will be L0" "next backup of physdbs will be L0" "next backup of logdbs will be L0" "next backup of datadbs will be L0" and today at 6 pm i could not do the L1 (as i used to do since several months) thanks
Have you tried to do a Level 0 backup first? Frank On Wed, Sep 22, 2010 at 5:56 PM, SMITH JOHN <daylight@webmails.com> wrote: > thank's a lot for your answer, > > how can i know if it's Ids that's requires this L0 or not ? i forgot to > tell > that's when i want to do an L1 archive it prompts that the version of > rootdbs > is too old... > > for exemple > > L0 yesterday at 6 pm > some batchs > L1 at 8pm > > today at 12 am i see, in the ids message log : > "next backup of rootdbs will be L0" > "next backup of physdbs will be L0" > "next backup of logdbs will be L0" > "next backup of datadbs will be L0" > > and today at 6 pm i could not do the L1 (as i used to do since several > months) > > thanks > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001485f197eac297220490ee8618
The following query will tell you if a level 0 is required or not, you can
also use
oncheck -pr or onstat -d to find this information out.
select sum( sysmaster:bitval(flags,'0x20000') ) as level_0_required from
sysmaster:sysdbstab
I do not recall what version of informix you are on, but there were some
issues with some internal
counter being incremented to fast and thus disabling level 1 and 2
archives.
John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 09/23/2010 08:00:48 AM:
> From:
>
> "FRANK" <yunyaoqu@gmail.com>
>
> To:
>
> ids@iiug.org
>
> Date:
>
> 09/23/2010 08:01 AM
>
> Subject:
>
> Re: level0 required daily [21442]
>
> Sent by:
>
> ids-bounces@iiug.org
>
> Have you tried to do a Level 0 backup first?
> Frank
>
> On Wed, Sep 22, 2010 at 5:56 PM, SMITH JOHN <daylight@webmails.com>
wrote:
>
> > thank's a lot for your answer,
> >
> > how can i know if it's Ids that's requires this L0 or not ? i forgot to
> > tell
> > that's when i want to do an L1 archive it prompts that the version of
> > rootdbs
> > is too old...
> >
> > for exemple
> >
> > L0 yesterday at 6 pm
> > some batchs
> > L1 at 8pm
> >
> > today at 12 am i see, in the ids message log :
> > "next backup of rootdbs will be L0"
> > "next backup of physdbs will be L0"
> > "next backup of logdbs will be L0"
> > "next backup of datadbs will be L0"
> >
> > and today at 6 pm i could not do the L1 (as i used to do since several
> > months)
> >
> > thanks
> >
> >
> >
> >
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --001485f197eac297220490ee8618
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
it's IDS 9.40.FC9 the query returns the number 5, what does it mean ?
It means you have 5 dbspaces which can not take level 0 archives John F. Miller III STSM, Embedability Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) |------------> | From: | |------------> >--------------------------------------------------------------------= -----------------------------------------------------------------------= -------| |"SMITH JOHN" <daylight@webmails.com> = = | >--------------------------------------------------------------------= -----------------------------------------------------------------------= -------| |------------> | To: | |------------> >--------------------------------------------------------------------= -----------------------------------------------------------------------= -------| |ids@iiug.org = = | >--------------------------------------------------------------------= -----------------------------------------------------------------------= -------| |------------> | Date: | |------------> >--------------------------------------------------------------------= -----------------------------------------------------------------------= -------| |09/23/2010 11:55 AM = = | >--------------------------------------------------------------------= -----------------------------------------------------------------------= -------| |------------> | Subject: | |------------> >--------------------------------------------------------------------= -----------------------------------------------------------------------= -------| |Re: level0 required daily [21446] = = | >--------------------------------------------------------------------= -----------------------------------------------------------------------= -------| |------------> | Sent by: | |------------> >--------------------------------------------------------------------= -----------------------------------------------------------------------= -------| |ids-bounces@iiug.org = = | >--------------------------------------------------------------------= -----------------------------------------------------------------------= -------| it's IDS 9.40.FC9 the query returns the number 5, what does it mean ? ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =
but on the message log i see : next archive for rootdbs will be level-0 next archive for physdbs will be level-0 .... i really don't understand anything
I guess I am not sure what you do not understand. The messages output below indicate that you system has had enough activity that the next time you= take an archive it MUST be a level 0. The server is telling you, you may NO= T take a level 1 or level 2 archive of the dbspace listed in the message.= These message say nothing about your need for an archive now, just that you are prevented from doing a level 1 or 2 archive. John F. Miller III STSM, Embedability Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) |------------> | From: | |------------> >--------------------------------------------------------------------= -----------------------------------------------------------------------= -------| |"SMITH JOHN" <daylight@webmails.com> = = | >--------------------------------------------------------------------= -----------------------------------------------------------------------= -------| |------------> | To: | |------------> >--------------------------------------------------------------------= -----------------------------------------------------------------------= -------| |ids@iiug.org = = | >--------------------------------------------------------------------= -----------------------------------------------------------------------= -------| |------------> | Date: | |------------> >--------------------------------------------------------------------= -----------------------------------------------------------------------= -------| |09/23/2010 02:01 PM = = | >--------------------------------------------------------------------= -----------------------------------------------------------------------= -------| |------------> | Subject: | |------------> >--------------------------------------------------------------------= -----------------------------------------------------------------------= -------| |Re: level0 required daily [21448] = = | >--------------------------------------------------------------------= -----------------------------------------------------------------------= -------| |------------> | Sent by: | |------------> >--------------------------------------------------------------------= -----------------------------------------------------------------------= -------| |ids-bounces@iiug.org = = | >--------------------------------------------------------------------= -----------------------------------------------------------------------= -------| but on the message log i see : next archive for rootdbs will be level-0 next archive for physdbs will be level-0 ..... i really don't understand anything ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =
i used to to a level 1 archive daily and the L0 only on week end. since 15 days ago , i can't do the L1 archives because i have a message "the ...of rootdbs is too old" and i must do a L0 archive.
What kind of media are you using to store the level0? Has your level0 expired or been purged? -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of SMITH JOHN Sent: Friday, September 24, 2010 5:19 AM To: ids@iiug.org Subject: Re: level0 required daily [21453] i used to to a level 1 archive daily and the L0 only on week end. since 15 days ago , i can't do the L1 archives because i have a message "the ....of rootdbs is too old" and i must do a L0 archive. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Why don't you do a Level 0 backup now? It won't hurt you , just help you. By the way, How long will it take for a Level 0 backup of your database? Frank On Fri, Sep 24, 2010 at 6:19 AM, SMITH JOHN <daylight@webmails.com> wrote: > i used to to a level 1 archive daily and the L0 only on week end. > > since 15 days ago , i can't do the L1 archives because i have a message > "the > ....of rootdbs is too old" and i must do a L0 archive. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001485f44c52ca15d30491028911
Hi, You're almost on the week-end. SO, why don't you make a L0 archive as requested by the engine ? Regards, Henri MOUKOURI Le 24/09/2010 11:19, SMITH JOHN a écrit : > i used to to a level 1 archive daily and the L0 only on week end. > > since 15 days ago , i can't do the L1 archives because i have a message "the > ....of rootdbs is too old" and i must do a L0 archive. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
i'm doing a L0 everyday, since 15 days ago it takes 1 hour i just want to know what does change for Ids to every day needs a L0, 15 days ago i was doing only L1 , and L0 at the week end
Your level 0 backup only takes 1 hour, If I were you, I would do a level 0 back up every day(cron job) and have enough logical spaces ready for at least more than one day ( also backup logical logs), then you would be worry free! If some thing happens, you have a solid Level 0 backup and all logical logs( which have the latest activities happened after the last level 0 back) ready, so you are safe. Thanks, Frank On Fri, Sep 24, 2010 at 1:20 PM, SMITH JOHN <daylight@webmails.com> wrote: > i'm doing a L0 everyday, since 15 days ago > it takes 1 hour > > i just want to know what does change for Ids to every day needs a L0, > 15 days ago i was doing only L1 , and L0 at the week end > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001485f199f00e1ae30491051d38
i agree with you, but a my L0 archive takes 200 go but the L1 one takes less than 1 go :), it's also a matter of disk space :).
OK, so did you take a Level 0 archive since that message began to appear? What John is telling you is that Informix has calculated that there has been so much update activity on those five dbspaces that a level 0 archive will run faster than a level 1 or level 2 archive, so you must take a level 0 archive. John can confirm whether or not your version suffers from the bug that prematurely sets the "level 0 required" flag, but whether that is true or not, you still have no choice but to take a level 0 archive. FYI, in case no one mentioned this before, 9.40 went out or support almost a year ago, you REALLY should upgrade to 11.50. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Sep 24, 2010 at 6:19 AM, SMITH JOHN <daylight@webmails.com> wrote: > i used to to a level 1 archive daily and the L0 only on week end. > > since 15 days ago , i can't do the L1 archives because i have a message > "the > ....of rootdbs is too old" and i must do a L0 archive. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016e68db7888d99ea04912a38ce