Sybase IQ 修改默认存储过程，监控表空间到GB级别

于 2019-07-30 17:17:00 发布
阅读量444 收藏
点赞数
CC 4.0 BY-SA版权
原文链接：http://www.cnblogs.com/BrokenSwitch/p/11271370.html
本文介绍了一个SQL Server中用于管理IQ DBSpace的存储过程sp_iqdbspace_ex。该过程能够获取DBSpace的相关信息，包括类型、状态、使用情况等，并返回这些信息供管理员监控和管理。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >


CREATE OR REPLACE PROCEDURE dbo.sp_iqdbspace_ex( 
  in "dbspaceName" varchar(128) default null ) 
result( 
  "DBSpaceName" varchar(128),
  "DBSpaceType" varchar(12),
  "Writable" char(1),
  "Online" char(1),
  "Usage" varchar(3),
  "UsageSize" varchar(10),
  "TotalSize" varchar(5),
  "Reserve" varchar(5),
  "NumFiles" integer,
  "NumRWFiles" integer,
  "Stripingon" char(1),
  "StripeSize" varchar(5),
  "BlkTypes" varchar(255),
  "OkToDrop" char(1),
  "lsname" varchar(128) ) 
on exception resume
sql security invoker
begin
  declare local temporary table "iq_dbspace_temp"(
    "FileId" smallint null,
    "RWMode" char(3) null,
    "IQOnline" char(1) null,
    "BlksTotal" unsigned bigint null,
    "BlksInUse" unsigned bigint null,
    "BlksReserve" unsigned bigint null,
    "FirstBlock" unsigned bigint null,
    "BlkSize" unsigned integer null,
    "VerBlks" unsigned bigint null,
    "BlksOldVer" unsigned bigint null,
    "BlksDropAtCP" unsigned bigint null,
    "FLBlks" unsigned bigint null,
    "ROFLBlks" unsigned bigint null,
    "DBIDBlks" unsigned bigint null,
    "CMIDBlks" unsigned bigint null,
    "IABlks" unsigned bigint null,
    "IUBlks" unsigned bigint null,
    "TUBlks" unsigned bigint null,
    "CUBlks" unsigned bigint null,
    "BackupBlks" unsigned bigint null,
    "CPLogBlks" unsigned bigint null,
    "HDRBlks" unsigned bigint null,
    "GFLBlks" unsigned bigint null,
    "SegType" varchar(12) null,
    "CanDrop" char(1) null,
    "isSecNode" char(1) null,
    "PFLBlks" unsigned bigint null,
    "lsid" unsigned bigint null,
    "mirrrorfileid" varchar(128) null,
    "serverID" unsigned integer null,
    "PFLBlksInUse" unsigned bigint null,
    "PFLBlksCommitLog" unsigned bigint null,
    "file_name" long varchar null,
    "IsDASSharedFile" char(1) null,
    ) in "SYSTEM" on commit preserve rows;
  declare local temporary table "iq_dbspace_name_map"(
    "internal_dbspace_name" char(128) not null,
    "external_dbspace_name" char(128) not null,
    ) in "SYSTEM" on commit preserve rows;
  declare "dbspaceName_literal" varchar(128);
  select "str_replace"("dbspaceName",'"',null) into "dbspaceName_literal";
  if not exists(select * from "SYSDBSPACE","SYSDBFILE","SYSIQDBFILE"
      where("dbspace_name" = "dbspaceName_literal" or "dbspaceName_literal" is null)
      and "SYSDBSPACE"."store_type" = 2
      and "SYSDBFILE"."dbspace_id" = "SYSDBSPACE"."dbspace_id"
      and "SYSDBFILE"."dbfile_id" = "SYSIQDBFILE"."dbfile_id"
      and "start_block" > 0) then
    raiserror 17816 'IQ dbspace '''+"dbspaceName"+''' not found.';
    return
  end if;
  insert into "iq_dbspace_name_map" select "dbspace_name","dbspace_name" from "SYSDBSPACE";
  if("locate"("lcase"("property"('CommandLine')),'-hes') <> 0) then
    update "iq_dbspace_name_map" set "external_dbspace_name" = 'ES_SYSTEM' where "internal_dbspace_name" = 'IQ_SYSTEM_MAIN';
    update "iq_dbspace_name_map" set "external_dbspace_name" = 'ES_TEMP' where "internal_dbspace_name" = 'IQ_SYSTEM_TEMP';
    update "iq_dbspace_name_map" set "external_dbspace_name" = 'ES_DELTA' where "internal_dbspace_name" = 'IQ_SYSTEM_RLV'
  end if;
  execute immediate with quotes on
    'iq utilities main into iq_dbspace_temp dbspace info ' || "dbspaceName";
  select "n"."external_dbspace_name" as "DBSpaceName",
    "min"("SegType") as "DBSpaceType",
    (case when "max"("iqd"."read_write") = 'T' and "max"("RWMode") = 'RW' then 'T' else 'F' end) as "Writable",
    (case when "max"("iqd"."online") = 'T' and "max"("IQOnline") = 'T' then 'T' else 'F' end) as "Online",
    (case when "d"."dbspace_name" = 'IQ_SYSTEM_TEMP' or "max"("isSecNode") = 'F' then convert(varchar(3),"truncnum"("ceiling"("sum"("BlksInUse")*100/"sum"("BlksTotal")),0)) else 'NA' end) as "Usage",
    convert(varchar(10),cast("truncnum"("sum"("BlksInUse")*"min"("BlkSize")/1024/1024/1024,2) as decimal(8,2)))
     || 'G' as "UsageSize",
     convert(varchar(4),"truncnum"("sum"("BlksTotal")*"min"("BlkSize")/"power"(1024,convert(integer,"IsNull"("log"("sum"("BlksTotal")*"min"("BlkSize")),1)/"log"(1024))),2))
     || "substr"('BKMGTP',convert(integer,"IsNull"("log"("sum"("BlksTotal")*"min"("BlkSize")),0)/"log"(1024))+1,1) as "TotalSize",
    convert(varchar(4),"truncnum"("sum"("BlksReserve")*"min"("BlkSize")/"power"(1024,convert(integer,"IsNull"("log"("sum"("BlksReserve")*"min"("BlkSize")),1)/"log"(1024))),2))
     || "substr"('BKMGTP',convert(integer,"IsNull"("log"("sum"("BlksReserve")*"min"("BlkSize")),0)/"log"(1024))+1,1) as "Reserve",
    "count"() as "NumFiles",
    "sum"(case when "RWMode" = 'RW' then 1 else 0 end) as "NumRWFiles",
    "striping_on" as "StripingON",
    convert(varchar(4),"truncnum"("iqd"."stripe_size_kb"/"power"(1024,convert(integer,"IsNull"("log"("iqd"."stripe_size_kb"),1)/"log"(1024))),2))
     || "substr"('BKMGTP',convert(integer,"IsNull"("log"("iqd"."stripe_size_kb"*1024),0)/"log"(1024))+1,1) as "StripeSize",
    "replace"(convert(varchar(21),"sum"("HDRBlks")) || 'H','0H','')
     || "Replace"(',' || convert(varchar(21),"sum"("FLBlks")) || 'F',',0F','')
     || "replace"(',' || convert(varchar(21),"sum"("DBIDBlks")) || 'D',',0D','')
     || "replace"(',' || convert(varchar(21),"sum"("VerBlks")) || 'A',',0A','')
     || "replace"(',' || convert(varchar(21),"sum"("BlksOldVer")) || 'O',',0O','')
     || "replace"(',' || convert(varchar(21),"sum"("BlksDropAtCP")) || 'X',',0X','')
     || "replace"(',' || convert(varchar(21),"sum"("CMIDBlks")) || 'M',',0M','')
     || "replace"(',' || convert(varchar(21),"sum"("IABlks")) || 'I',',0I','')
     || "replace"(',' || convert(varchar(21),"sum"("IUBlks")) || 'U',',0U','')
     || "replace"(',' || convert(varchar(21),"sum"("TUBlks")) || 'T',',0T','')
     || "replace"(',' || convert(varchar(21),"sum"("CUBlks")) || 'N',',0N','')
     || "replace"(',' || convert(varchar(21),"sum"("BackupBlks")) || 'B',',0B','')
     || "replace"(',' || convert(varchar(21),"sum"("CPLogBlks")) || 'C',',0C','')
     || "replace"(',' || convert(varchar(21),"sum"("PFLBlks")) || 'R',',0R','')
     || "replace"(',' || convert(varchar(21),"sum"("PFLBlksInUse")) || 'RU',',0RU','')
     || "replace"(',' || convert(varchar(21),"sum"("PFLBlksCommitLog")) || 'RC',',0RC','')
     || "replace"(',' || convert(varchar(21),"sum"("GFLBlks")) || 'G',',0G','') as "BlkTypes",
    if("sum"("VerBlks")+"sum"("BlksOldVer")+"sum"("BlksDropAtCP")+"sum"("GFLBlks")+"sum"("BackupBlks")+"sum"("CPLogBlks")+"sum"("PFLBlks")) = 0
    and "min"("CanDrop") = 'T'
    and not exists(select * from "SYS"."SYSTAB" where "dbspace_id" = "d"."dbspace_id")
    and not exists(select * from "SYS"."SYSIDX" where "dbspace_id" = "d"."dbspace_id")
    and not exists(select * from "SYS"."SYSIQPARTITIONCOLUMN" where "dbspace_id" = "d"."dbspace_id")
    and "d"."dbspace_id" <> 16384
    and "d"."dbspace_id" <> 16385
    and "d"."dbspace_id" <> 32702 then
      'Y' else 'N' endif as "OkToDrop",
    "l"."ls_name" as "Logical Server Name"
    from "iq_dbspace_temp"
      join "SYSDBFILE" as "f"
      on "iq_dbspace_temp"."FileId" = "f"."dbfile_id"
      join "SYSDBSPACE" as "d"
      on "f"."dbspace_id" = "d"."dbspace_id"
      join "SYSIQDBSPACE" as "iqd"
      on "iqd"."dbspace_id" = "d"."dbspace_id"
      join "iq_dbspace_name_map" as "n"
      on "d"."dbspace_name" = "n"."internal_dbspace_name"
      left outer join "SYSIQLOGICALSERVER" as "l"
      on "iq_dbspace_temp"."lsid" = "l"."ls_id"
    group by "d"."dbspace_name","n"."external_dbspace_name","d"."dbspace_id","iqd"."stripe_size_kb","striping_on","l"."ls_name"
    order by 1 asc;
  drop table "iq_dbspace_temp";
  drop table "iq_dbspace_name_map"
end
GO

方便监控使用

转载于:https://www.cnblogs.com/BrokenSwitch/p/11271370.html

MrCode0828
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值