Does anyone know the batch command to create synonyms?

There is a REFRESH SYNONYM command that works on one previously created synonym. I searched the documentation CD for WebFOCUS 5.3.2 and have found no documentation on this command.

Since there is an annoying limitation on creating synonyms using the WebFOCUS 5.3.4 Server Console, I would like to use a batch command to create synonyms for about 500 DB2 tables.

Francis,

I think the syntax is something like -

CREATE SYNONYM [synonym name] FOR [target] AT [server]

If you parameterise the items in square brackets it should allow you to batch it.

T

This message has been edited. Last edited by: Tony A,

Just checked the syntax (memory from EDA 4.3.1 ish) and it is now -

CREATE SYNONYM [app name]/[new synonym] FOR [target table] DBMS [dbms type i.e. SQLMSS] AT [adapter name]
END

The END is important

You can also issue a DROP SYNONYM [app name]/[new synonym] if required

T

Tony, thanks very much. I suppose I will have to issue a DROP in case the synonym already exists, unless there's a replace command.

Where did you find the syntax - I've been looking everywhere!

Thanks.

Tony, I built a little fex to read the DB2 SYSCAT TABLES table and then use Dialog Manager to run DROP and CREATE SYNONYM command for each table.

Worked like a charm.

Cheers.

Francis, found and copied this long ago:

APP HOLD IBISAMP
SQL SQLORA
SELECT OWNER, TABLE_NAME FROM ALL_TABLES;
TABLE FILE SQLOUT
PRINT TABLE_NAME
WHERE TABLE_NAME NOT CONTAINS '$' OR '/' OR 'SMP';
WHERE OWNER CONTAINS 'PCGRP';
ON TABLE HOLD AS LISTHOLD FORMAT ALPHA
END

APP DELETE TEST
APP CREATE TEST
-RUN
APP HOLD TEST
FILEDEF LISTHOLD DISK E:\ibi\apps\ibisamp\listhold.ftm
-RUN

-SET &TABLE=' ';
-LP
-READ LISTHOLD &TABLE.A30.
-IF &IORETURN NE 0 GOTO NOMORE;
CREATE SYNONYM &TABLE FOR &TABLE DBMS SQLORA
END
-GOTO LP
-NOMORE

Tom, thank you. My fex is very similar.

SET HOLDFORMAT      = ALPHA
-RUN

TABLE FILE SYSCAT_TABLES
PRINT 
TABSCHEMA
TABNAME
WHERE TABSCHEMA IN ('BASEL', 'BSLC');
-***WHERE RECORDLIMIT EQ 10
ON TABLE HOLD AS HOLD01
END
-RUN
-SET &NBR_TABLES = &LINES;

-REPEAT END_REP001 &NBR_TABLES TIMES

-READ HOLD01 NOCLOSE &TABSCHEMA.A128. &TABNAME.A128.

-SET &TAB_NAME = &TABSCHEMA || '.' || &TABNAME;
-SET &SYN_NAME = &TABSCHEMA || '_' || &TABNAME;

-*CREATE SYNONYM [app name]/[new synonym] FOR [target table] DBMS [dbms type i.e. SQLMSS] AT [adapter name]

DROP SYNONYM basel/&SYN_NAME
END
-RUN

CREATE SYNONYM basel/&SYN_NAME FOR &TAB_NAME DBMS DB2 AT BSL
END
-RUN

-END_REP001

Cheers.

This message has been edited. Last edited by: Francis Mariani,