Temp Table Question
We have a stored procedure that creates a temp table (and drops it at the end). However, the procedure aborted before the drop, and the connection stayed active. Is there any way to determine which session "owns" a temporary table (assuming I know the name of the temp table)? Thanks, [cid:image003.jpg@01CB1DF4.D2246F60]Jeffrey J. Mitchell Database Administrator, West Interactive Corporation 11650 Miracle Hills Drive, Omaha NE 68154 402-716-0500 | Cell 402-321-7443 | jjmitchell@west.com<mailto:jjmitchell@west.com> This electronic message transmission, including any attachments, contains information from West Corporation which may be confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not the intended recipient, be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic transmission in error, please notify the sender immediately by a "reply to sender only" message and destroy all electronic and hard copies of the communication, including attachments.
I picked up this script from somewhere (prob iiug) some years back. It
includes the login and also reports on which dbspace the temptable occupies.
TMPFILE='/tmp/temptables'
dt=`date '+%m%d%y_%H%M%S'`
echo "UNLOAD TO '$TMPFILE' DELIMITER ' '\\\\
SELECT trim(n.dbsname) || ':' || \\\\
trim(n.owner) || ':' || \\\\
trim(n.tabname) table, \\\\
DBINFO('DBSPACE', i.ti_partnum) dbspace, \\\\
i.ti_nptotal allocated_pages, \\\\
i.ti_nrows number_rows \\\\
FROM systabnames n, systabinfo i \\\\
WHERE (bitval(i.ti_flags, '0x0020') = 1 \\\\
OR bitval(i.ti_flags, '0x0040') = 1) \\\\
AND i.ti_partnum = n.partnum \\\\
ORDER BY 1,2" | dbaccess sysmaster
cat $TMPFILE | awk '
BEGIN { printf("TEMP TABLES BY DBSPACE\\
")
printf(" pages Total\\
")
printf("db:owner:table dbspace allocated Rows\\
")
ptot = 0
}
{
if($1 != "")
{
printf("%-30s %-15s %-13d %-10d\\
", $1, $2, $3, $4)
ptot += $3
}
}
END { printf("\\
Total Pages Allocated to temp tables: %10d\\
", ptot)
}' > ./temptable_report.$dt
HTH
Zev Berezin
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Mitchell, Jeffrey J.
Sent: Wednesday, July 07, 2010 5:53 PM
To: ids@iiug.org
Subject: Temp Table Question [20532]
We have a stored procedure that creates a temp table (and drops it at the
end). However, the procedure aborted before the drop, and the connection
stayed active.
Is there any way to determine which session "owns" a temporary table (assuming
I know the name of the temp table)?
Thanks,
[cid:image003.jpg@01CB1DF4.D2246F60]Jeffrey J. Mitchell
Database Administrator, West Interactive Corporation
11650 Miracle Hills Drive, Omaha NE 68154
402-716-0500 | Cell 402-321-7443 |
jjmitchell@west.com<mailto:jjmitchell@west.com>
This electronic message transmission, including any attachments, contains
information from West Corporation which may be confidential or privileged. The
information is intended to be for the use of the individual or entity named
above. If you are not the intended recipient, be aware that any disclosure,
copying, distribution or use of the contents of this information is
prohibited.
If you have received this electronic transmission in error, please notify the
sender immediately by a "reply to sender only" message and destroy all
electronic and hard copies of the communication, including attachments.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.