dbimport compressed tables
User attempted to create compressed tables during dbimport by adding COMPRESSED clause to CREATE TABLE statements. Andrew Grantham clarified that dbimport decompresses data during export, requiring recompression afterward. He recommended creating tables with COMPRESSED option in the SQL file, then using alternative tools (dbload, LOAD FROM, HPL) instead of dbimport to load data, allowing more flexibility and faster loading.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Dear all,
I tried to edit .sql file before dbimport by adding
create table yyy
(
....
)
fragment by expression
... in datadbs
extent size 256 next size 256
lock mode row
compressed;
following the IBM url
https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqls.doc/id
s_sqs_2309.htm#ids_sqs_2309
CREATE TABLE t(c int, d int) EXTENT SIZE 32 NEXT SIZE 32 COMPRESSED;
My question is do I have some syntax problem or the dbimport utility cannot
create the table this way(compressed)?
Thank you
Aleksandar
Alexander,
From memory the compression feature is only available if you are using the
Enterprise Editions. Do you know what license type you have? Also please
provide the Informix version and OS you are using. What error are you getting?
Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986>; for Windows 10
From: ALEKSANDAR IVANOVSKI<mailto:aleksandar.ivanovski@gmail.com>
Sent: Tuesday, June 13, 2017 2:12 PM
To: ids@iiug.org<mailto:ids@iiug.org>
Subject: dbimport compressed tables [39358]
Dear all,
I tried to edit .sql file before dbimport by adding
create table yyy
(
.....
)
fragment by expression
.... in datadbs
extent size 256 next size 256
lock mode row
compressed;
following the IBM url
https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqls.doc/id
s_sqs_2309.htm#ids_sqs_2309
CREATE TABLE t(c int, d int) EXTENT SIZE 32 NEXT SIZE 32 COMPRESSED;
My question is do I have some syntax problem or the dbimport utility cannot
create the table this way(compressed)?
Thank you
Aleksandar
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Andrew, SLES server version 12. (intel 64bit) IDS 11.7 FC4 Assume it is Enterprise Edition since I can allocate more than 16GB memory to the engine which I think is the limit for the standard. Thank you, Aleksandar
Alexander,
Have a look at this link. It answers your question.
https://www.ibm.com/support/knowledgecenter/SSGU8G_11.70.0/com.ibm.mig.doc/ids_m
ig_113.htm
Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986>; for Windows 10
From: ALEKSANDAR IVANOVSKI<mailto:aleksandar.ivanovski@gmail.com>
Sent: Tuesday, June 13, 2017 3:17 PM
To: ids@iiug.org<mailto:ids@iiug.org>
Subject: Re: RE: dbimport compressed tables [39361]
Andrew,
SLES server version 12. (intel 64bit)
IDS 11.7 FC4
Assume it is Enterprise Edition since I can allocate more than 16GB memory to
the engine which I think is the limit for the standard.
Thank you,
Aleksandar
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Thank you for the fast response,
I know that dbexport does decompress the data.
However I dont understand if i should first import all the data uncompressed:
You must recompress the data after you use the dbimport utility to import the
data.
and later issue compress repack shrink to some of the tables, or it can be
done "on the fly" meaning to switch on automatic compression before loading
the data as it is stated in
https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqls.doc/id
s_sqs_2309.htm#ids_sqs_2309
Thank you,
Aleksandar
Alexander,
If you use dbimport then my reading of this is that you must first load the
data uncompressed and then compress it afterwards. However this is where you
can use other ways to do this. Dbimport consists of a SQL file and loads of
data files with odd names. You are at liberty to create the tables using the
sql file with the compressed option set and then use some other tool ( dbload,
load from, HPL for example ) to load the data instead of using dbimport. Its
more administration but you have more flexibility. For example you can
parallel load tables using multiple load files. Dont forget to create the
tables only and not the indexes or triggers or other objects until after the
data has been loaded. It will load faster. You can create the tables raw as
well and then alter them afterwards to normal. You have loads of options. How
big is your DB and how big are you largest tables? It might be worth looking
at Art kagels scripts as he may have already visited this problem and come up
with a nice program to do it all for you.
Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986>; for Windows 10
From: ALEKSANDAR IVANOVSKI<mailto:aleksandar.ivanovski@gmail.com>
Sent: Tuesday, June 13, 2017 3:29 PM
To: ids@iiug.org<mailto:ids@iiug.org>
Subject: Re: RE: RE: dbimport compressed tables [39363]
Thank you for the fast response,
I know that dbexport does decompress the data.
However I dont understand if i should first import all the data uncompressed:
You must recompress the data after you use the dbimport utility to import the
data.
and later issue compress repack shrink to some of the tables, or it can be
done "on the fly" meaning to switch on automatic compression before loading
the data as it is stated in
https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqls.doc/id
s_sqs_2309.htm#ids_sqs_2309
Thank you,
Aleksandar
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.