Create synonyms between two tables in different databases Informix

user_11363473 0
Viewed 102

I have the database gcd001 that contains multiple tables tab1, tab2, tab3 which contain data, and a second database dde002 that contains the same tables tab1, tab2, tab3 but with no data.

I'm trying to create a script that will create synonyms between the tables of these two databases. I tried something like:

# !/bin/sh
# USAGE : cresyn.sh {dtabase_with_data} {dtabase_no_data} {Table}

dtabase_with_data=$1
dtabase_no_data=$2
Table=$3

dbaccess $1 CREATE SYNONYM $1 FOR $2:$3;

done

but it didn't work and I really don't know how to do it. Can you help?

informix
1 Answers

The DB-Access command doesn't interpret command line arguments like that. Its usage pattern is:

dbaccess [-|database] [-|script]

It will add the .sql extension to the script argument if you don't specify it (so it only executes .sql files). Without the script option, it goes interactive having select the named database. Without the named database, it goes interactive too, without pre-selecting a database. The dash for a script name means "read standard input". The dash for a database name means "do not pre-select a database"; presumably the script will select or create the database itself.

You probably want to use:

if [ $# != 3 ]
then
    echo "Incorrect arguments.  Usage: $0 db_with db_without tablename" >&2
    exit 1
fi

dbaccess $2 - <<EOF

CREATE SYNONYM $3 FOR $1:$3;

EOF

This connects to the 'database without data' ($2) and creates a synonym with the same name as the table ($3) referencing that table in 'database with data' ($1). You could use the variables you set but didn't use too.

Note that the tables tab1 .. tab3 cannot exist in the 'database without data' before you create the synonyms. Or you have to use an alternative name for the synonyms, such as syn_tab1, .. syn_tab3 (but then your script needs to take arguments for the existing table name and the synonym to be created.

user_151680
Related