General Page
This replaces the IBM Data Replication Community Wiki How to Create Many Nicknames Easily Page.
Way to create a lot of nicknames at one time.
Way to create a lot of nicknames at one time.
If you are not very familiar with IBM's federated database technology, I recommend you give the basics a try before using this tip.
The most common way to create a lot of nicknames at one time is to use a graphical user interface. However, you have a lot more flexibility if you use SQL and scripts. For example, I sometimes start with a variation of the following SQL query from a DB2 command line:
db2 select concat(concat(concat(concat(concat('create nickname ',concat(strip(creator),concat('.',name))),' for MYSERVER.'),strip(creator)),'.'),strip(name)) from sysibm.systables where creator='ASN'
The output is something like the following:
create nickname ASN.IBMSNAP_SUBS_EVENT for MYSERVER.ASN.IBMSNAP_SUBS_EVENT
create nickname ASN.IBMSNAP_SUBS_MEMBR for MYSERVER.ASN.IBMSNAP_SUBS_MEMBR
create nickname ASN.IBMSNAP_SUBS_SET for MYSERVER.ASN.IBMSNAP_SUBS_SET
create nickname ASN.IBMSNAP_SUBS_STMTS for MYSERVER.ASN.IBMSNAP_SUBS_STMTS
create nickname ASN.IBMSNAP_UOW for MYSERVER.ASN.IBMSNAP_UOW
create nickname ASN.IBMSNAP_SUBS_MEMBR for MYSERVER.ASN.IBMSNAP_SUBS_MEMBR
create nickname ASN.IBMSNAP_SUBS_SET for MYSERVER.ASN.IBMSNAP_SUBS_SET
create nickname ASN.IBMSNAP_SUBS_STMTS for MYSERVER.ASN.IBMSNAP_SUBS_STMTS
create nickname ASN.IBMSNAP_UOW for MYSERVER.ASN.IBMSNAP_UOW
Naturally, you'll have to make some adjustments. For example:
Change MYSERVER to your server name you specified on your Create Server statement.
Change the predicate to match the source tables for which you want to have nicknames.
Add a semi-colon to the SQL so that it shows up at the end of the statement for use in DB2 SQL scripts.
Use multiple select statements if the predicates get too hairy :)
If you the nicknames would have the same name as existing tables, then consider changing the first "strip(creator)" to a string like 'nick1' or to the same value as the server name specified on the server mapping statement.
Change the predicate to match the source tables for which you want to have nicknames.
Add a semi-colon to the SQL so that it shows up at the end of the statement for use in DB2 SQL scripts.
Use multiple select statements if the predicates get too hairy :)
If you the nicknames would have the same name as existing tables, then consider changing the first "strip(creator)" to a string like 'nick1' or to the same value as the server name specified on the server mapping statement.
If you're good with shell scripts, you may find them a handy tool for use with SQL.
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSTRGZ","label":"InfoSphere Data Replication"},"Component":"","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF012","label":"IBM i"},{"code":"PF016","label":"Linux"},{"code":"PF051","label":"Linux on IBM Z Systems"},{"code":"PF033","label":"Windows"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Was this topic helpful?
Document Information
Modified date:
03 December 2019
UID
ibm11105017