Oninit cdi / Archive
Talk to an expert →

Checking database status : Checkpoint

Posted in 2009

Topics: Stored Procedures & SPL, Logging & Checkpoints

jak aka — — source: IIUG Forums & Mailing Lists

Hi , I'd like to know the database current status of instance if db is blocking (by checkpoint ) or not . I want to use SQL statement to query, I don't want to use onstat to check. Can I select from sysmaster tables or any function ? Where can I find that infomation ? For example : select ... from ; (status) Blocked:CKPT The same as display in onstat heading ; IBM Informix Dynamic Server Version 10.00.FC6W4X2 -- On-Line (CKPT REQ) -- Up 4 days 05:20:48 -- 4262912 KbytesBlocked:CKPT Thank you Jakkrit A.

↪ replying to jak aka
Richard Snoke — — source: IIUG Forums & Mailing Lists

Hi, Start from the following and modify it to suit your needs. select DBINFO('Version','full') as Version, c.type as role, CASE a.sh_mode when 1 then "Fast Recovery" when 2 then "Quiescent" when 3 then “Backup" when 4 then “Shutdown" when 5 then "On-Line" when 6 then “Aborting" when 7 then "Admin" else "Unknown" END as Mode, CURRENT – DBINFO("UTC_TO_DATETIME", a.sh_boottime ) as UpTime, (sum(b.seg_size)/1024)::integer || " Kbytes" as shared_memory from sysshmvals a, syssegments b, sysha_nodes c group by 1,2,3,4 ; Cheers, Dick Snoke IBM Data Management - ChannelWorks dsnoke@us.ibm.com (404) 487-1595 From: "jak aka" <jakkritakeng@yahoo.com> To: ids@iiug.org Date: 03/12/09 12:24 AM Subject: Checking database status : Checkpoint [15105] Sent by: ids-bounces@iiug.org Hi , I'd like to know the database current status of instance if db is blocking (by checkpoint ) or not . I want to use SQL statement to query, I don't want to use onstat to check. Can I select from sysmaster tables or any function ? Where can I find that infomation ? For example : select ... from ; (status) Blocked:CKPT The same as display in onstat heading ; IBM Informix Dynamic Server Version 10.00.FC6W4X2 -- On-Line (CKPT REQ) --Up 4 days 05:20:48 -- 4262912 Kbytes Blocked:CKPT Thank you Jakkrit A. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Richard Snoke
jak aka — — source: IIUG Forums & Mailing Lists

Thank you very much. select case sh_cpflag when 1 then "doing checkpoint" else "normal state" end case ,current from sysshmvals a; ________________________________ From: Richard Snoke <dsnoke@us.ibm.com> To: ids@iiug.org Sent: Friday, March 13, 2009 12:27:14 AM Subject: Re: Checking database status : Checkpoint [15118] Hi, Start from the following and modify it to suit your needs. select DBINFO('Version','full') as Version, c.type as role, CASE a.sh_mode when 1 then "Fast Recovery" when 2 then "Quiescent" when 3 then Backup" when 4 then Shutdown" when 5 then "On-Line" when 6 then Aborting" when 7 then "Admin" else "Unknown" END as Mode, CURRENT DBINFO("UTC_TO_DATETIME", a.sh_boottime ) as UpTime, (sum(b.seg_size)/1024)::integer || " Kbytes" as shared_memory from sysshmvals a, syssegments b, sysha_nodes c group by 1,2,3,4 ; Cheers, Dick Snoke IBM Data Management - ChannelWorks dsnoke@us.ibm.com (404) 487-1595 From: "jak aka" <jakkritakeng@yahoo.com> To: ids@iiug.org Date: 03/12/09 12:24 AM Subject: Checking database status : Checkpoint [15105] Sent by: ids-bounces@iiug.org Hi , I'd like to know the database current status of instance if db is blocking (by checkpoint ) or not . I want to use SQL statement to query, I don't want to use onstat to check. Can I select from sysmaster tables or any function ? Where can I find that infomation ? For example : select ... from ; (status) Blocked:CKPT The same as display in onstat heading ; IBM Informix Dynamic Server Version 10.00.FC6W4X2 -- On-Line (CKPT REQ) --Up 4 days 05:20:48 -- 4262912 Kbytes Blocked:CKPT Thank you Jakkrit A. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Glossary