Backup smartblobs seperate and move from 11.70 to 14.10
Marc Demhartner asked how to move smartblobs from 11.70 to 14.10 by cold backup. Art Kagel suggested ER first, then external tables, noting dbexport works only for whole databases and there is no dbunload. He pointed to myschema and myexport to generate external table scripts. Doug Lawry said a separate file is written per smart blob column, as with UNLOAD, and cited a load/unload article.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hello, is there a smooth way to backup smartblobs from 11.70 to 14.10 (different server)? System would be RHEL7 and RHEL8 Thanks ------------------------------ Marc Demhartner ------------------------------
If the smartblob spaces are logged, and the table has a primary key, you can use ER to move the table from one server to a new one and maintain them in sync until turnover! ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------
Thanks but this would be cold backup with manual move to different server ------------------------------ Marc Demhartner ------------------------------
Ahh. Then unload by insertingbthe data into an external table and reload by reading from an external table. ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------
Ok, that step i wanted to avoid. Is there a way to use dbexport or dbunload ?
------------------------------
Marc Demhartner
------------------------------
dbexport can do it, but it only exports the entire database so not a single table or handful of tables. There is no dbunload, only dbload.
You can use myexport in external table mode. If you don't want to have to hand build the external tables, myschema can generate them for you:
art@Elezar-II:~/GoogleDrive/MyConsulting/Scripts$ myschema -d art -t systables --export-scripts /dev/null
Writing full schema DDL to: /dev/null
art@Elezar-II:~/GoogleDrive/MyConsulting/Scripts$ dir art.systables.export.sql
-rw-rw-r-- 1 art art 827 Oct 5 10:14 art.systables.export.sql
art@Elezar-II:~/GoogleDrive/MyConsulting/Scripts$ cat art.systables.export.sql
CREATE EXTERNAL TABLE IF NOT EXISTS systables_external (
tabname VARCHAR(128,0),
owner CHAR(32),
partnum INTEGER,
tabid SERIAL(3421),
rowsize SMALLINT,
ncols SMALLINT,
nindexes SMALLINT,
nrows FLOAT,
created DATE,
version INTEGER,
tabtype CHAR(1),
locklevel CHAR(1),
npused FLOAT,
fextsize INTEGER,
nextsize INTEGER,
flags SMALLINT,
site VARCHAR(128,0),
dbname VARCHAR(128,0),
type_xid INTEGER,
am_id INTEGER,
pagesize INTEGER,
ustlowts DATETIME YEAR TO FRACTION(5),
secpolicyid INTEGER,
protgranularity CHAR(1),
statchange SMALLINT,
statlevel CHAR(1)
) USING ( FORMAT "DELIMITED",
DATAFILES ( "disk:/home/art/GoogleDrive/MyConsulting/Scripts/systables.unl" ),
DELIMITER "|", DELUXE, ESCAPE ON );
INSERT INTO systables_myexport
SELECT * FROM systables;
DROP TABLE systables_myexport;------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
------------------------------
Oops, that got away from me. You may have to edit the file to add a path to the blob file location, see the SQL Syntax manual for details. ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------
There is a separate file per smart blob column in the same location as the main external table file, following the same conventions as the UNLOAD statement: https://www.ibm.com/docs/en/informix-servers/14.10?topic=file-unloading-smart-large-objects See also another easier way to use this: https://www.oninitgroup.com/faq-items/load-and-unload-functions ------------------------------ Doug Lawry Oninit Consulting ------------------------------