On 23rd Jan 1998 lester@advancedatatools.com (Lester Knutsen) wrote:-
A couple of things, you must run ipload on a x-windows system, and have the DISPLAY envirmoment variable set. Also if you are running it as another user then the one you logged in as we need to issue the "xhost + " command to allow access to the display. (e.g logging in as lester, then su to informix and try to run ipload will fail unles I have executed the xhost command)
On 13th Oct 1998 helmut.leininger@bull.de (Helmut Leininger) wrote:-
Between Unix systems I would set up .rhosts or hosts.equiv. But how can I do something equivalent on NT systems?
I found the solution myself. You have to put a hosts.equiv file into c:\winnt\system32\drivers\etc.
On 19th Feb 1998 aroustv@towers.com (Vardan Aroustamian) wrote:-
select number, uniqid, physchunk(physloc) physloc, chknum, name
from syslogfil l, syschktab c, sysdbstab d
where physchunk(physloc) = c.chknum and
c.dbsnum = d.dbsnum
order by 1;
It's working in OnLine 7.2x
On 3rd Mar 1998 Martin.Berns@Materna.De (Martin Berns) wrote:-
the following select (against sysmaster) should do what you want:
select s.name dbspace, n.dbsname database, sum(ti_nptotal) total, sum(ti_npused) used, sum(ti_npdata) data, sum(ti_npused) - sum(ti_npdata) idx from systabinfo i,systabnames n, sysdbspaces s where i.ti_partnum = n.partnum and partdbsnum(i.ti_partnum)=s.dbsnum group by 1,2 order by 1,2
On 10th Mar 1998 satriguy@aol.com (SaTriGuy) wrote:-
If you are on anything since 7.11, I wouldn't worry too much about the "less than 8 extent" rule. In version 5, we used an extent table in memory for each tablespace which was only 8 entries large. For any extent past the eighth, we had to examine the systables table for that database directly. This is why we made such a big deal about the "eight extents".
With 7.11+, we dynamically allocate memory so that all of the extents are in memory. Thus the "less than extents" rule is not nearly so important.
However, if you are really going to have this many tables on a 7.1x or 7.2x system, you might want to increase your in memory dictionary somewhat. This is managed by the DD_HASHSIZE and DD_HASHMAX onconfig variables. These are undocumented so you will need to contact tech support to get information on how to use them.
On 20th Jan 1998 jparker@epsilon.com (Jack Parker) wrote:-
Check the violations tables. If you did not name them specifically they will have a name of table_vio and table_dia in your database - these should have the rows (in the vio table) and the reason (in the dia table). You can join the two tables using the tuple_id and look up the reason from the HPL manual.
On 1st May 1998 richard_thomas@yes.optus.com.au (Richard Thomas) wrote:-
There is a limitation referred to in TFM that warns against using 2-digit century in fragment expressions - and I realise you're not doing that. But the manual (IMHO) does not go far enough to warn you that the fragment expression seems to be stored with the $DBDATE that's effective at the time of the ALTER FRAGMENT execution. Subsequent queries that use a different $DBDATE run into all sorts of problems.
We have a number of fragmentation strategies that rely on date-ranges (eg 5 days to a DBspace etc) and used to have all manner of grief with performance and elimination etc.
I found the solution to this is to manipulate the fragment expressions using the Informix internal representation of the date (ie 1998-05-01 35915). Whilst it reduces the 'readability' of the dbschema and sysfragments info, all our fragment elimination problems immediately disappeared.
So try:
FRAGMENT BY EXPRESSION > inv_dte = 35519 IN dbs01 > inv_dte = 35526 IN dbs02
as a work-around and see how you go.
A simple way to perform the translation from DATE to INTEGER is to do this
SELECT date_field, TRUNC(DATE(date_field)) AS int_value FROM ...
On 5th July 1998 ccremid@saadev.saa.noaa.gov (Tino) wrote:-
Check the DBDATE environment variable used at the time of dbexport and see if its value matches the one that you are using at the time of dbimport.
On 11th May 1998 kagel@bloomberg.com (Art S. Kagel) wrote:-
You have stumbled on a little known fact. Sorting is faster to filesystem space than to temp table space. And since you do not care about the safety of those sort-work files that's OK.
Even better still, for large sorts like index builds and UPDATE STATISTICS, set the environment variable PSORT_DBTEMP to as many different filesystems as possible (at least 3, keeping in mind that the smallest will limit the size of the sort that is possible). Also, contrary to the documentation, setting PSORT_NPROCS to a value between 20 and 40 will also speed the sorting as long as you can afford to allocate so many resources to that one task. You might see the time drop to 15 or 20 minutes.
On 14th May 1998 rajam@worldnet.att.net (Idiot) wrote:-
You did not mention, what version of Informix you are using. Assuming you are using 7.22. Do the following,
SET INDEXES idx_name DISABLED;
START VIOLATIONS TABLE FOR tab_name;
SET INDEXES idx_name ENABLED even better, SET INDEXES idx_name FILTERING
WITHOUT ERROR
load data
All the duplicate records will go to the violations and diagnostic table. You can debug the violations table to correct the offending records and load them into the main table.
You will have to have:
DBA privilege or
Owner of the target table and have resource privilege OR
Alter privilege on the target table and have resource privilege.
Read the documentation for START VIOLATIONS TABLE in the syntax guide.
On 20th May 1998 sch@pdx.informix.com (Stephen Hemminger) wrote:-
Some of this is fixed in 7.3.
The trick is to bring server up with the environment variable CDRBLOCKOUT set (any value will do). Then do the dbexport, then restart server without environment set.
example:
% onmode -yk # shutdown server
% CDRBLOCKOUT=on oninit -iy # start server
% dbexport
% onmode -yk
% oninit -iy
Note: any transactions that happen while server is running with CDRBLOCKOUT will not be replicated!
On 23rd Jun 1998 davek@summitdata.com (David Kosenko) wrote:-
Peter Lancashire You, even as DBA, do not have GRANT privs on the view - you gave them
away when you created the view as owned by user query. Run the GRANT as
user query, and it should work ok. While you are at it, as user query
GRANT ALL ON ptreatments TO DBA; On 29th Oct 1998 clem@informix.com (Clem Akins) wrote:- Ontape to a file *IS* supported by Informix. It has been for a
while now. You can quote June Tong's internal TechInfo (not
the one available to customers via the web site, but an internal
one) entry #6125 to any Informix Technical Support engineer who
still thinks otherwise. (Thanks, June!) However, the ontape program expects a tape drive, and
behaves accordingly. It is *your* responsibility to do all the
things necessary to satisfy the program's expectations and requirements.
(Things like simulating an operator pressing return, switching
files when the tape size limit is reached, handling rewind device
expectations, log file output and overflow, etc.) You can find
some example shell scripts which perform these functions at the
web site of the International Informix Users Group at
www.iiug.org The question of how large the output file can become without an
error is highly dependent on O/S version as well as the version
of ontape. In Informix Dynamic Server versions <7.2 there was
a definite 2GB limit on the filesize that ontape would support.
In versions >=7.2 the limit comes from the O/S, and may still be
2GB. It is not too difficult to write a program that would
shuffle disk files once they reach the maximum size, convincing
ontape that an operator is changing tapes. Of course, the
challenge comes in labeling and saving these files well enough
to supply them to ontape in the right order at restore time. As with any critical system, you should *thoroughly* test the
archive and restore functions, including your shop's procedures
for saving disk layout information, documenting the process
well enough for someone besides yourself to handle a disaster,
and ensuring that management understands that you have done your
job as a DBA and deserve a raise. (The flip side is that if you
haven't, and disaster strikes, you'll be looking for a job.) On 19th Oct 1998 tschaefe@mindspring.com (Tim Schaefer) wrote:- This is one I use for XPS. Your challenge should you decide to accept
is to remove the dbslice layer of the problem, or simply use it with XPS
as it is. I know your intent is probably for 7.x, but I present this
not just for you, but for others out there who may be using XPS. To be
sure, this solution serves only a minority of you out there. But the
future is coming, especially now with our new friends from RedBrick. XPS works at one extra layer beyond that which exists for the 7.x engine.
DBslices are logical groupings of dbspaces across nodes. I would challenge Informix to show table level information like this in the
IECC for XPS. Many of you out there don't realize this, but there are no
less than 3 IECC programs, probably more. One for 7.x, one for XPS that
points to UNIX, and one for XPS that works only with NT. :-) The code presented would allow a DBA the total picture, not stopping like it
currently does at the dbspace. Some of the most important priorities a DBA has
are in understanding where things are, how much space is available, and how much
space is used. Currently only slices and spaces are shown in the IECC, but
table information is also necessary. On 6th May 1998 richard_thomas@yes.optus.com.au (Richard Thomas) wrote:- I'm presuming that ontape/onbar etc aren't going to do it for you. There
is an alternative, but it requires a little bit of work on your behalf.
I hate dbexport - if your database is bigger than about 15GB, it is
hopelessly inefficient. It also doesn't let you move tables around and
play with extent sizes etc prior to execution (in v5 you couldn't even
use -ss). Here's what I did when we migrated from v5 to v7 a while back: Generate a full dbschema and edit it to suit your new DBspaces, extents
etc. Ensure logging is turned off and run it on your new instance. Then
on the old instance, execute the following: The order by is to get the tables in rough order from largest to
smallest. Then use awk or perl to read this file and generate 4 unload
scripts in a round-robin fashion, where each one unloads to a different
disk. Depending on what you're trying to achieve, you could possibly use
onunload instead. Something like this: Then execute the four unload_*.sql scripts in parallel. You can run as
many of these as you have disks/capacity. I chose to use four. As they finish, kick off the corresponding dbload script. One of the
other benefits of this method over dbimport is that you don't have to
start from scratch if you have a problem. You can fairly easily restart
the dbloads from virtually any point. Actually, when I did this I had one dbload script per table, and three
processes polling for completed unloads. As they were identified, the
corresponding dbload would be executed. Using this method I moved over
20GB of data in about 10 hours, including rebuilding indexes, statistics
etc. The previously attempted dbexport was killed after 56 hours, 'cos
we were running out of outage and were nowhere near complete! On 15th Oct 1998 jmiller@informix.com (John F. Miller III) wrote:- Below is a write-up my co-worker did on some quick and usefull
steps in getting onbar/HDR/Legato running. I hope
they help. Resident tables On 13th Jan 1999 Vardan.Aroustamian@chase.com (Vardan Aroustamian) wrote:- Actually it is last column in onstat -t (flag 2000)
You can get that information also from sysmasters On 18th Jan 1999 dmeyer9@email.msn.com (David Meyer) wrote:- It is good to see people trying to use this extraordinary
Informix tool. I use the ALARM functions to notify beginning
dba's of critical conditions within the database. Besides being
a GUI that allows you view all Informix parameters and operating
functions.... it is just a hot product! Now, for your question, I am not certain where you are in the steps
to run oncockpit so I will list a few steps for you to check, I hope this
helps... have fun! BTW - a good book that covers all this and more
is Carlton Doe's - Informix Online Dynamic Server Handbook. 1) oncockpit is a GUI client-server application. 'onprobe', the server
program must be running and communicating with the instance. onprobe
is defined as a 'service' with it's own instance alias and corresponding
entries in the $SQLHOSTS and /etc/services files. 2) Since 'oncockpit' is a GUI, your DISPLAY environment must be set
to your IP address or host name of the machine that the GUI will display to: 3) onprobe and oncockpit have their own command line arguments. I think
that you have to have root permissions to run onprobe but I am not sure. An example
of how to start onprobe and oncockpit: 4) check the logs that are created to debug any problems runnning the client
or server applications. You should really perform a 'ps -eaf |grep onprobe' to
verify that the program is running before you execute oncockpit.... remember to
check the oncockpitand onprobe logs. On 3rd Feb 1999 psilva@informix.com (Paulo Silva) wrote:- After installing IDS on NT, you should notice a new Shared Folder
calledSQEXPLN, under your %INFORMIXDIR% (usually c:\informix\sqexpln). This folder keeps all outputs for all users, in the form %USERNAME%.out,
instead of the usual sqexplain.out On 6th Feb 1999 gunstho@uni-muenster.de (Dirk Gunsthoevel) wrote:- You have to set db_locale to your locale (I assume something like FR_fr.1252 for you)
BEFORE creating the database. If you are still using isql dont create the database in it. It will NOT use the
db_locale setting. Use dbaccess instead. On 23rd Mar 1999 dua1@my-dejanews.com (Juri Dovgart) wrote:- PDQ has impact on the index builds - when PDQPRIORITY > 0. It's called
vertical parallelism. Infx perform parallel scans, sorts and uses memory,
allocated for PDQ for sorts. Here some advices about index builds : On 6th May 1999 icc@injersey.infi.net (Vic Glass) wrote:- Formula to calculate ~ bytes that an index will use: (sum of column sizes + 9) * rows * 1.25 For example, if there is a table: then the index space needed for 1,000,000 rows would be approximately: On 6th May 1999 mcollins@us.dhl.com (Mark Collins) wrote:- Slight correction to the earlier answer - the formula listed is for attached
indexes. If you put the indexes in their own dbspace, they are detached, and
the formula changes to (sum of column sizes + 13) * rows * 1.25. The extra four
bytes store the partition number of the table (or fragment) in which the indexed
row is found. I'm assuming that the "1.25" is a general rule-of-thumb for
estimating the overhead of non-leaf pages, but that method is only a rough
approximation at best. Another thing that needs to be addressed when
calculating index space is FILLFACTOR. On 23rd June 1999 gdewinter@spf.fairchildsemi.com (Greg Dewinter) wrote:- -197 ISAM error: Partition recently appended to; can't open for write or
logging. This error is generally seen after a High Performance Load in Express
mode. The only way to correct this is to do a level 0 archive of the
effectted dbspaces. Express mode HPL loads data into a new extent and then appends the extent
to the table when the entire load is complete. That is why it is able to
load all the rows with no logging. On 28th June 1999 jleffler@earthlink.net (Jonathan Leffler) wrote:- If you're on a civilized system (Unix or variants), and you're using
a sufficiently civilized server (OnLine, IDS, etc) and a sufficiently
recent version of ESQL/C (primarily CSDK 2.x), then you can set the
SQLIDEBUG environment variable to a value such as 2:/tmp/sqli.out
and the application will log all the data sent back and forth. You
can then decipher the data with the sqliprint program. The actual
data file will have an underscore and a process id after what you
specified in SQLIDEBUG. The SQLIDEBUG environment variable has worked
for quite some time (6.00?), but getting hold of sqliprint has been
harder until it was distributed with CSDK. You can also use a value
1:/tmp/sqli.out to get some sort of ASCII dump, but it isn't as useful
as the binary dump (IIRC; I've not used it more than once). On 16th July 1999 RRABE@PROMUS.com (Rick Rabe) wrote:- I'm not familiar with Netbackup, but use Networker on Siemens-Pyramid. My
BAR_MAX_BACKUP is set to 8. One thing I have found helpful is altering
bar_action, bar_instance, bar_object in the sysutils database to row-level
locking instead of page-level locking. On 9th July 1999 sanformix@hotmail.com (Santanu Das) wrote:- The following steps may be used to setup Legato storage manager software.
For more info you may refer to Legato Installation guide. The following is brief overview of the steps required to configure Legato
for use with ON-Bar. If you want to execute any onbar comands from the command line, Legato
requires that the two environment variable, NSR_DATA_VOLUME_POOL and
NSR_LOG_VOLUME_POOL, must be set to the same values specified in the client
panel. If you follow the installation manual instructions, these pools will
be named, DBMIData and DBMILog. On 28th July 1999 kagel@bloomberg.net (Art S. Kagel) wrote:- Get my dostats.ec utility from the IIUG Software Repository and run 5-10 copies,
on different tables, at once with PDQPRIORITY=100/(#copies). Here is an ksh/awk
script to create the script to run all the needed dostats in parallel groups: Then to generate a multiple dostats script, assume you named the above genstats:
genstats mydatabase 5 >updstats.sh On 16th August 1999 vardana@infogain.com (Vardan Aroustamian) wrote:- I used to use some variations of this query to check temporary tables: On 17th August 1999 jakesalomon@my-deja.com (Jacob Salomon) wrote:- After receiving your reply I got to a little experimenting. The pattern
I noticed is the the 0x20 flag - bitval(p.flags, 32) - is the marker of
any kind of temp table. I noticed that SORTTEMP and HASHTEMP tables
have some other flags set but all of them has this one flag on. Now my query for abuses of temp tables is: On 17th August 1999 vardana@infogain.com (Vardan Aroustamian) wrote:- You can set resident only particular fragment of fragmented table On 27th August 2014 art.kagel@gmail.com (Art Kagel) wrote:
Memory resident tables were only supported in v7.30 (though the command was still supported but no-op until v11.10).
+No doubt this is simple but I can't see it.
+
+The following was all done as the DBA user.
....
+I granted privileges on all tables in the database like this:
+grant select on
8.33 Can I run ontape to a disk file?
8.34 How can I list all tables in a dbspace?
Dbslice
+
+-dbspace
+-dbspace
+-table
+-table
+-dbspace
Dbslice
+
+-dbspace
+-table
+-table
+-dbspace
+-table
+-table
+-dbspace
+-dbspace
# BEGIN
#!/bin/sh
################################################################################
# begin doc
#
# Program: XDBtree
#
# Author: Tim Schaefer
# Data Design Technologies, Inc.
# www.datad.com
#
# Login: tschaefe@mindspring.com
#
# Created: May 1998
#
# Description: XDBtree reports on tables in dbspaces.
# The report is based on your ONCONFIG setting.
#
# Usage: XDBtree
#
# end doc
################################################################################
# sysdbslices
#
# Column name Type Nulls
# dbslice_num smallint yes
# name char(18) yes
# ndbspaces smallint yes
# is_rootslice integer yes
# is_mirrored integer yes
# is_blobslice integer yes
# is_temp integer yes
#
################################################################################
# syscmdbspaces
#
# Column name Type Nulls
#
# dbsnum smallint yes
# name char(18) yes
# fchunk smallint yes
# nchunks smallint yes
# home_cosvr smallint yes
# current_cosvr smallint yes
# dbslice_num smallint yes
# dbslice_ordinal smallint yes
# is_root integer yes
# is_mirrored integer yes
# is_blobspace integer yes
# is_temp integer yes
#
################################################################################
get_db_info()
{
date
dbaccess sysmaster 2>/dev/null <<+
set isolation to dirty read;
unload to /tmp/systree.dat
select
sysdbslices.name,
syscmdbspaces.name,
syscmdbspaces.dbslice_num,
syscmdbspaces.dbsnum,
syscmdbspaces.fchunk,
sysextents.dbsname ,
sysextents.tabname ,
sysextents.start_chunk ,
sysextents.start_offset ,
sysextents.size
from syscmdbspaces, sysdbslices, sysextents
where sysdbslices.dbslice_num = syscmdbspaces.dbslice_num
and sysextents.start_chunk = syscmdbspaces.fchunk
order by
sysdbslices.name,
syscmdbspaces.name,
sysextents.start_chunk,
sysextents.start_offset,
sysextents.tabname
+
}
################################################################################
produce_rpt()
{
awk -F"|" ' BEGIN {
dbslice_name="" ;
dbspace_name="" ;
dbslice_num="" ;
dbspace_num="" ;
fchunk="" ;
dbsname="" ;
tabname="" ;
start_chunk="" ;
start_offset="" ;
size="" ;
ldbslice_name="" ;
ldbspace_name="" ;
ldbslice_num="" ;
ldbspace_num="" ;
lfchunk="" ;
ldbsname="" ;
ltabname="" ;
lstart_chunk="" ;
lstart_offset="" ;
lsize="" ;
size_cntr=0 ;
}
{
dbslice_name=$1 ;
dbspace_name=$2 ;
dbslice_num=$3 ;
dbspace_num=$4 ;
fchunk=$5 ;
dbsname=$6 ;
tabname=$7 ;
start_chunk=$8 ;
start_offset=$9 ;
size=$10 ;
{ if ( tabname == "TBLSpace" ) { { tabname = "" } } }
{ if ( ldbslice_num == dbslice_num ) { { dbslice_num = "" } } }
{ if ( ldbslice_name == dbslice_name ) { { dbslice_name = "" } } }
{ if ( ldbspace_num == dbspace_num ) { { dbspace_num = "" } } }
{ if ( lstart_chunk == start_chunk ) { { start_chunk = "" } } }
{ if ( ldbspace_name == dbspace_name ) { { dbspace_name = "" } } }
{ if ( dbspace_name == dbsname ) { { dbsname = "" } } }
{ if ( ldbsname == dbsname ) { { dbname = "" } } }
{ if ( ltabname == tabname ) { { tabame = "" } } }
{ printf( "%3s %-18s %3s %3s %-18s %-18s %-18s %10s %10s\n", dbslice_num, dbslice_name, dbspace_num, start_chunk, dbspace_name, dbsname, tabname, start_offset, size ) }
# { printf( "%3s %-18s %3s %3s %s %s %-20s %10s %10s\n", dbslice_num, dbslice_name, dbspace_num, start_chunk, dbspace_name, dbsname, tabname, start_offset, size ) }
last_chk=$1 ;
ldbslice_name=$1 ;
ldbspace_name=$2 ;
ldbslice_num=$3 ;
ldbspace_num=$4 ;
lfchunk=$5 ;
ldbsname=$6 ;
ltabname=$7 ;
lstart_chunk=$8 ;
lstart_offset=$9 ;
lsize=$10;
}
' /tmp/systree.dat
}
################################################################################
>/tmp/systree.dat
get_db_info
produce_rpt
# >/tmp/systree.dat
# END
8.35 Is there anything faster than dbexport?
UNLOAD TO "table.data" DELIMITER "|"
SELECT tabname, ncols, ncols * nrows
FROM systables
WHERE tabid > 99
ORDER BY 3 DESC;
perl -e '
open(Unld0, "> unload_1.sql") || die;
open(Unld1, "> unload_2.sql") || die;
open(Unld2, "> unload_3.sql") || die;
open(Unld3, "> unload_4.sql") || die;
open(Dbld0, "> unload_1.cmd") || die;
open(Dbld1, "> unload_2.cmd") || die;
open(Dbld2, "> unload_3.cmd") || die;
open(Dbld3, "> unload_4.cmd") || die;
@dsk =3D ("/disk_a","/disk_b","/disk_c","/disk_d"); #array of disks =
for writes
while(<>){
($tab, $cols) =3D split(/\|/);
$r=3D$n++%4; #modulo to cycle through scripts: $n =3D Row number
$uno=3Dsprintf("Unld%d", $r);
$dno=3Dsprintf("Dbld%d", $r);
printf $uno "UNLOAD TO %s/%s.unl DELIMITER \"|\"\n", $dsk[$r], $tab;
printf $uno " SELECT * FROM %s;\n\n",$tab;
printf $dno "FILE %s/%s.unl DELIMITER \"|\" %d;\n", $dsk[$r], $tab, =
$cols;
printf $dno "INSERT INTO %s;\n\n", $tab;
}' table.data
8.36 How do I setup OnBar and Legato?
8.37 How do I get information about tables from sysmaster?
select tabname
from sysptntab p, systabnames n
where p.partnum = n.partnum
and trunc(flags/8192) = 1;
8.38 How do I use Oncockpit?
$INFORMIXSERVER=shmcci;export INFORMIXSERVER;
onprobe -service cpit_cci -log /home/informix/onprobe_djm.log -severity
severity.djm &
$INFORMIXSERVER=xxxcci;export INFORMIXSERVER;
oncockpit -service cpit_cci -log /home/informix/oncockpit_djm.log -fg
red -bg gray &
8.39 Where do sqexplain.out's appear under NT?
8.40 How do I use locales under Informix?
8.41 How do I use optimize index builds?
8.42 How do I calculate how much space an index will use?
ix1: (4 +9) * 1000000 * 1.25 ----> 16,250,000 bytes or 16,250 Kb
ix2: (9+9) * 1000000 * 1.25 -----> 22,500,000 bytes or 22,500 Kb
ix3: (25+25+9) * 1000000 * 1.25 ---> 73,750,000 bytes or 73,750 Kb
8.43 Why do I get error -197?
8.44 How do I log the queries informix receives?
8.45 Any hints for running more than one onbar process at a time?
8.46 How can I use Legato with onbar?
8.47 How can I update statistics in parallel?
#! /usr/bin/ksh
if [[ $# -lt 2 ]]; then
echo Usage: $0 database #copies
8.48 How do I locate temporary tables?
select tabname,
case
when bitval( p.flags, 32 ) = 1
then 'sys_temp'
when bitval( p.flags, 64 ) = 1
then 'usr_temp'
when bitval( p.flags, 128 ) = 1
then 'sort_file'
end type,
hex(n.partnum) h_n_partnum,
n.partnum n_partnum,
-- n.owner,
-- hex(p.flags) h_p_flags,
name dbspace_name
from sysptnhdr p,
systabnames n,
sysdbstab d
where p.partnum = n.partnum
and partdbsnum( n.partnum ) = d.dbsnum
and ( bitval( p.flags, 32 ) = 1 -- System created Temp Table
or bitval( p.flags, 64 ) = 1 -- User created Temp Table
or bitval( p.flags, 128 ) = 1 ) -- Sort File
);
select t.dbsname, t.tabname,
hex(p.partnum) partition, hex(p.flags) pflags
from sysmaster:systabnames t, sysmaster:sysptnhdr p
where t.partnum = p.partnum
and bitval(p.flags,32) = 1 -- Looking for temp tables
and trunc(p.partnum / 1048576) -- Filter: Only temps not in
in (select dbsnum -- temp dbspace
from sysdbspaces where is_temp = 0)
order by dbsname, tabname, partition
8.49 How do I set tables memory resident?
SET TABLE your_table MEMORY_RESIDENT;
SET TABLE your_table NON_RESIDENT;
SET TABLE your_table ( dbspace1, dbspace2 ) MEMORY_RESIDENT;