IDS Forum

RE: move database to another dbspace

Posted By: Sergio Peres
Date: Thursday, 11 January 2018, at 1:17 p.m.

In Response To: Re: move database to another dbspace (Habichtsberg, Reinhard)

Hi Reinhard,

I'm going to try, I'll tell you after about the results.
I'm very grateful for your help and work on comments also.

Thanks a lot,
SP

Sérgio Peres
Administração de Sistemas

AIRC - Associação de Informática da Região Centro
Coimbra iParque, Lote nº 15,
3040-540 Antanhol - Coimbra
Portugal
+351-239850536 | Directo
+351-919324330 | Movel
sergio.peres@airc.pt | e-Mail
www.airc.pt | website

-----Mensagem original-----
De: Habichtsberg, Reinhard [mailto:RHabichtsberg@arz-emmendingen.de]
Enviada: 11 de janeiro de 2018 10:45
Para: ids@iiug.org
Cc: Sergio Peres <sergio.peres@airc.pt>
Assunto: Re: move database to another dbspace [40477]

Hi,

the following sample worked for me. Please understand it merely as a frame. Source and target table are in the same database in the same informix server-instance.
If you have to work on different databases and/or instances you have to qualify this in the dbaccess commands.

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Load tables via external tables und pipes

ATTENTION: To use pipes as shown in the sample you have to configure sufficient virtual processors for FIFO.

Minimum: onmode -p +2 FIFO

The script shows how table content can be transferred from one to another table while using external tables and named pipes.

The target table should be of type (raw) to avoid logging. External tables in deluxe mode are also possible because it's possible to configure commits.

The script below is a sample. If you put some loops around you can automatically transfer entire databases.
Something like "select tabname from systables where tabtype ='T' and tabid > 99" one-time in a file, reading the file in a loop an use tabname as variable in the script.

My last tests with pipes on Solaris were successful while the databases or server-instances were located on the same host.

#!/usr/bin/bash

#---------------------------------------------------------
# if in a loop:
# call the script: scriptname tabname
# tabname in $1
# a_test=$1
# assumed tabname is the same in source and target database # b_test=$a_test # replace a_test and b_test by ${a_test} and ${b_test} # replace ext_a_test and ext_b_test by ext_${a_test}and ext_${b_test}
#---------------------------------------------------------

date > migtest.log
# To be save: Delete existing Named Pipes from previous tests rm /tmp/tpipe_in rm /tmp/tpipe_out # If not done by hand earlier, change target table to type (raw), reasonable in loops over entire databases #echo "alter table b_test type (raw);"
# dbaccess zftest -<<EOF
# alter table b_test type (raw);
# for safety
# drop table ext_a_test;
# drop table ext_b_test;
#EOF
# ERG=$?
# if [ $ERG -ne 0 ]
# then
# echo "error beim alter table b_test type (raw)"
# fi
#echo "Next=Return"
#read taste

# Create Named Pipes, one for writing in the external table, one for reading. Two pipes are necessary because there exists an exclusive lock while writing. Only one Pipe would be blocked.
echo PIPE anlegen:
echo "mkfifo /tmp/tpipe_in"
mkfifo /tmp/tpipe_in
ERG=$?
if [ $ERG -ne 0 ]
then

echo "error while creating pipe tpipe_in"; date>> migtest.log; exit 1
fi
echo "Next=Return"
read taste
echo PIPE anlegen:
echo "mkfifo /tmp/tpipe_out"
mkfifo /tmp/tpipe_out
ERG=$?
if [ $ERG -ne 0 ]
then

echo "error while creating pipe tpipe_out"; date>> migtest.log; exit 1
fi
echo "Next=Return"
read taste
# Creating the external table that is used for the import of the data into the target table. Format is "Informix" because it is fastest without any conversations.
echo Create external table for IMPORT
dbaccess zftest - <<EOF
CREATE EXTERNAL TABLE ext_b_test sameas b_test
USING (DATAFILES ('PIPE:/tmp/tpipe_in'),
FORMAT 'informix',
REJECTFILE 'b_test.log',
EXPRESS);
EOF
ERG=$?
if [ $ERG -ne 0 ]
then

echo "error while creating external table for IMPORT"; date>> migtest.log; exit 1
fi
echo "Next=Return"
read taste
# Creating the external table that is used for the export of the data from the source table.
echo "Create external table a_test in zftest and open for EXPORT"
dbaccess zftest - <<EOF
CREATE EXTERNAL TABLE ext_a_test SAMEAS a_test
USING (DATAFILES ('PIPE:/tmp/tpipe_out'),
FORMAT 'informix');
EOF
ERG=$?
if [ $ERG -ne 0 ]
then

echo "error while creating external table for EXPORT"; date>> migtest.log; exit 1
fi
echo "Next=Return"
read taste
# Connecting of the pipes as background process
echo open PIPE
echo "cat > /tmp/tpipe_in < /tmp/tpipe_out"
cat > /tmp/tpipe_in < /tmp/tpipe_out &
ERG=$?
if [ $ERG -ne 0 ]
then

echo "error while opening the pipes"; date>> migtest.log; exit 1
fi
# Opening of the reading process as background process
echo "Insert IMPORT command"
dbaccess zftest - <<EOF &
insert into b_test select * from ext_b_test; EOF
ERG=$?
if [ $ERG -ne 0 ]
then

echo "error at Insert IMPORT b_test"; date>> migtest.log; exit 1
fi
echo "Next=Return"
read taste
# Opening of the writing process
echo "Insert EXPORT Befehl"
dbaccess zftest - <<EOF
insert into ext_a_test select * from a_test; EOF
ERG=$?
if [ $ERG -ne 0 ]
then

echo "error at Insert EXPORT a_test"; date>> migtest.log; exit 1
fi
# The end is reached. The data are loaded into the target table.
echo EXPORT/IMPORT ready!

echo Sleeping 5 seconds; sleep 5
echo "Next=Return"
read taste
# echo "delete external tables "
#
# dbaccess zftest - <<EOF
# drop table ext_slda_abr_code;
#EOF
#ERG=$?
#if [ $ERG -ne 0 ]
#then
# echo "error at DROP external table EXPORT"
#fi
#
# dbaccess zftest - <<EOF
# drop table ext_n_slda_abr_code;
#EOF
#ERG=$?
#if [ $ERG -ne 0 ]
#then
# echo "error at DROP external table IMPORT"
#fi
# echo "delete PIPES"
# rm /tmp/tpipe_in
# rm /tmp/tpipe_out

HTH,
Reinhard.

-----Ursprüngliche Nachricht-----
Von: Sergio Peres [mailto:sergio.peres@airc.pt]
Gesendet: Mittwoch, 10. Januar 2018 19:39
An: Habichtsberg, Reinhard
Betreff: RE: move database to another dbspace [40477]

Hi,

It would be wonderful if you can send me some information and script also.
Don't be worried about comments, google is our friend and if I don't understand I will call translator :).
As I don't have blobspaces on this specific case your solution can help me for sure.

Thanks a lot and best regards,

SP

Sérgio Peres
Administração de Sistemas

AIRC - Associação de Informática da Região Centro Coimbra iParque, Lote nº 15, 3040-540 Antanhol - Coimbra Portugal
+351-239850536 | Directo 351-919324330
+| Movel
sergio.peres@airc.pt | e-Mail
www.airc.pt | website

-----Mensagem original-----
De: Habichtsberg, Reinhard [mailto:RHabichtsberg@arz-emmendingen.de]
Enviada: 10 de janeiro de 2018 18:33
Para: Sergio Peres <sergio.peres@airc.pt>
Assunto: Re: move database to another dbspace [40477]

Hi,

years ago I had do migrate from on SAN-Storage to another with hundreds of databases and thousands of tables.

My plan was: dbschema of the source databases and creating of the target databases from those dbschemas after placing desired changes in the dbschemas like dbspaces, extent sizes etc.

For each database a script that generates in a loop:
- for each table of the source database an external table with connection to a named pipe
- for each table of the target database an external table with connection to a named pipe
- a shell command that connects the named pipes
- a select into external table from source table at the source database
- a select into into target table from external table at the target database

External table operations are very fast.

If you use format "informix" they are even faster. I don't know if it works for all resp. user defined datatypes. IIRC there were some trouble with blob-fields (TEXT, BYTE).

If you are interested I could send you a sample script. Have a bit work on it to translate the comments to English.

HTH,
Reinhard.

-----Ursprüngliche Nachricht-----
Von: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Im Auftrag von SERGIO PERES
Gesendet: Dienstag, 9. Januar 2018 16:31
An: ids@iiug.org
Betreff: move database to another dbspace [40467]

Hi,

I have several databases to move to another dbspace as I reach the partitions limit. As I don't have enough time to do export/import, How can I do it? I try with onunload/onload but I'm getting several warnings like:
Warning: The given database or table contains column(s) of user defined datatype. Onload and Onunload do not handle such data types. The load may not be successful or may not load back the correct data.

Thanks in advance for any help,

SP
sergio.peres@airc.pt

*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.

*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.

Messages In This Thread

• move database to another dbspace
  SERGIO PERES -- Tuesday, 9 January 2018, at 10:31 a.m.
  • Re: move database to another dbspace
    MARK JALKIEWICZ -- Tuesday, 9 January 2018, at 11:27 a.m.
  • Re: move database to another dbspace
    Andreas Legner1 -- Tuesday, 9 January 2018, at 2:37 p.m.
    • Re: move database to another dbspace
      SERGIO PERES -- Tuesday, 9 January 2018, at 5:18 p.m.
      • RE: move database to another dbspace
        Everett Mills -- Tuesday, 9 January 2018, at 6:34 p.m.
        • Re: RE: move database to another dbspace
          SERGIO PERES -- Tuesday, 9 January 2018, at 7:14 p.m.
          • Re: RE: move database to another dbspace
            Alexandre Marini -- Tuesday, 9 January 2018, at 7:42 p.m.
  • Re: move database to another dbspace
    Habichtsberg, Reinhard -- Wednesday, 10 January 2018, at 1:32 p.m.
    • Re: move database to another dbspace
      Habichtsberg, Reinhard -- Thursday, 11 January 2018, at 5:45 a.m.
    • Re: move database to another dbspace
      Andreas Legner1 -- Thursday, 11 January 2018, at 6:40 a.m.
      • Re: move database to another dbspace
        Habichtsberg, Reinhard -- Thursday, 11 January 2018, at 11:53 a.m.
    • RE: move database to another dbspace
      Sergio Peres -- Thursday, 11 January 2018, at 1:17 p.m.
  • Re: move database to another dbspace
    Kern Doe -- Wednesday, 10 January 2018, at 5:25 p.m.
  • Re: move database to another dbspace
    Kern Doe -- Wednesday, 10 January 2018, at 5:31 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.