Svenson's  Bulletin Board Forum Index Svenson's Bulletin Board
Bulletin Board for boat building and model airplane building enthusiasts.
 
AlbumAlbum   FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Search found 37 matches
Svenson's Bulletin Board Forum Index
Author Message
  Topic: SQL to get num_execs / rows read percentage
craig

Replies: 1
Views: 17553

PostForum: DB2   Posted: Wed Nov 17, 2010 3:06 pm   Subject: SQL to get num_execs / rows read percentage
The following sql will return a percentage of rows read per execution. High rate may indicate indexing opportunities. It could also be from group by's or sums or such.


select cast(100-(cast((ca ...
  Topic: Recursive SQL split column data into rows
craig

Replies: 0
Views: 15735

PostForum: DB2   Posted: Tue Jun 15, 2010 6:25 am   Subject: Recursive SQL split column data into rows
with t(ordinal,string,tbcreator,tbname,indexname) as
(
select 1,colnames,tbcreator,tbname,name as indexname from sysibm.sysindexes
union all
select ordinal+1
,case locate('+',str ...
  Topic: DBI1167E DPF instance cannot be updated from non instance
craig

Replies: 0
Views: 13976

PostForum: DB2   Posted: Sun May 16, 2010 11:27 am   Subject: DBI1167E DPF instance cannot be updated from non instance
If you get "DBI1167E DPF instance cannot be updated from non instance owning nodes." then

a) change the db2nodes.cfg and set the catalog nodes hostname to the local host name.
b) run th ...
  Topic: sql to return tablespace names, nodegroup, pgsz, totalpages
craig

Replies: 0
Views: 13966

PostForum: DB2   Posted: Fri Sep 11, 2009 12:19 pm   Subject: sql to return tablespace names, nodegroup, pgsz, totalpages
with
tspacea as ( select 0 as p, TABLESPACE_NAME,sum(TOTAL_PAGES) as total_pages
from table( SNAPSHOT_CONTAINER ('',0)) as tspace1
group by tablespace_id,tablespace_name order by ...
  Topic: create snapshot tables ddl
craig

Replies: 0
Views: 13551

PostForum: DB2   Posted: Thu Feb 12, 2009 11:18 am   Subject: create snapshot tables ddl
create table dba.snapshot_dbm
as ( SELECT * FROM TABLE(SNAPSHOT_DBM( 0)) as pg ) definition only;
create table dba.snapshot_fcm
as ( SELECT * FROM TABLE(SNAPSHOT_FCM( 0)) as pg ) definition onl ...
  Topic: recover dropped table
craig

Replies: 0
Views: 12665

PostForum: DB2   Posted: Thu Dec 11, 2008 7:43 am   Subject: recover dropped table
list history dropped table all for DBSTUDY

restore database DBSTUDY tablespace (tspname,tspnme2) use tsm taken at 20040320123838 without rolling forward without prompting

rollforward database DB ...
  Topic: reduce tablespace - reclaim space - reset high water mark.
craig

Replies: 0
Views: 12357

PostForum: DB2   Posted: Wed Jul 09, 2008 4:12 pm   Subject: reduce tablespace - reclaim space - reset high water mark.
Reorg all tables in the tablespace you wish to reduce.

A) List tablespaces show detail to get tablespaceid to reset.

B) db2dart <dbname> /LHWM /TSI <tbspaceid> /np 0
run the ...
  Topic: sql for stored proc to copy big tables
craig

Replies: 0
Views: 12636

PostForum: DB2   Posted: Wed Nov 29, 2006 2:29 pm   Subject: sql for stored proc to copy big tables
-- run with db2 -c- -td@ -vf l | at now

connect to prodods1@

drop procedure db2ods.load_archive_stsomod()@

create procedure db2ods.load_archive_stsomod()
language sql
p1: begin
for v_ ...
  Topic: script to create alter tablespace stment with add containers
craig

Replies: 0
Views: 13497

PostForum: DB2   Posted: Thu Nov 02, 2006 3:33 pm   Subject: script to create alter tablespace stment with add containers
#!/bin/ksh

Spindles()
{
df -k | \
grep dasd | \
egrep -v 'log|100%|9[0-9]%' | \
sort +3 -n | \
awk '{ print $7 }'
}
...
  Topic: sql to snapshot tablespace percent free space < 20%
craig

Replies: 0
Views: 12888

PostForum: DB2   Posted: Mon Oct 23, 2006 3:36 pm   Subject: sql to snapshot tablespace percent free space < 20%
with cserv(dbname) as (select current server from sysibm.sysdummy1)
select dbname
,tablespace_name
,total_pages
,free_pages
,int(decimal(free_pages) / decimal(total_pages)*100) ...
  Topic: gen create tablespace statement, distribute across spindles
craig

Replies: 0
Views: 12327

PostForum: DB2   Posted: Mon Oct 23, 2006 1:52 pm   Subject: gen create tablespace statement, distribute across spindles
Usage()
{
echo 'usage: ctsp <dbname> <numpages> <tablespacename> <numcontainers> <pagesize>' >&2
exit 1
}

Spindles()
{
df -k | \
grep dasd | \
egre ...
  Topic: default database configuration settings
craig

Replies: 0
Views: 12035

PostForum: DB2   Posted: Fri Oct 20, 2006 7:41 am   Subject: default database configuration settings
db2 update db cfg for OWLODS1 using \
NEWLOGPATH /db2/dasd/log_dir/OWLODS1 \
APPLHEAPSZ 4096 \
STMTHEAP 8192 \
DBHEAP 4800 \
APP_CTL_HEAP_SZ 1024 \
SORTHEAP 2048 \
DBHEAP 8192 \
L ...
  Topic: db2 on linux settings for IO tuning.
craig

Replies: 0
Views: 12231

PostForum: DB2   Posted: Thu Sep 28, 2006 2:08 pm   Subject: db2 on linux settings for IO tuning.
db2set DB2_SCATTERED_IO=ON
db2set DB2LINUXAIO=true

CREATE REGULAR TABLESPACE TESTBSP MANAGED BY SYSTEM USING (/data/) NO FILE SYSTEM CACHING;

ALTER TABLESPACE TESTTBSP2 NO FILE SYSTEM CACH ...
  Topic: sql to assign row numbers to rows returned by select
craig

Replies: 0
Views: 12498

PostForum: DB2   Posted: Thu Sep 28, 2006 9:43 am   Subject: sql to assign row numbers to rows returned by select
SELECT * FROM ( SELECT
ROW_NUMBER() OVER (ORDER BY name) AS rownumber, name
FROM SYSIBM.SYSTABLES
) A
WHERE rownumber is not null;
  Topic: sql to generate sql to drop foreign keys
craig

Replies: 0
Views: 13034

PostForum: DB2   Posted: Thu Sep 07, 2006 1:58 pm   Subject: sql to generate sql to drop foreign keys
select 'alter table '||rtrim(tbcreator)||'.'||tbname||' drop foreign key '||name||';'
from SYSIBM.SYSTABCONST where tbcreator='MDI' and constraintyp='F'
 
Page 1 of 3 Goto page 1, 2, 3  Next
All times are GMT - 8 Hours
Jump to:  


Powered by phpBB © 2001, 2005 phpBB Group