I use the CREATE SYNONYM command in a FEX to generate metadata. This command does not include the CONNECTION attribute in the generated ACX file.

eg.

Metadata generated from the WebFOCUS Server Console:

 
SEGNAME=BSLC_STAT_NODE, TABLENAME=BSLC.STAT_NODE, CONNECTION=bsl, KEYS=1, $

Metadata generated via the CREATE SYNONYM command:

 
SEGNAME=WF_RPT_CODES, TABLENAME=HBRUSPACE.WF_RPT_CODES, KEYS=0, $

How do I get the CONNECTION attribute using the CREATE SYNONYM command?

Thanks,

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

Well, that was quick!

I neglected to include the "AT" attribute in the CREATE SYNONYM command.

CREATE SYNONYM baseapp/BSLSIT_SYSSTAT_INDEXES FOR SYSSTAT.INDEXES DBMS DB2 AT BSLSIT

Case closed!

And I thought that I may be able to help you as we generate our metadata the same way, and ours does have the connection attribute.

The funny thing is that we remove ours.

Waz,

Maybe you now can remove the "AT connection-name"

Cheers,

I guess it's morning for you.

My brother is temporarily somewhere in Australia, say hello!

That may be difficult has its a big place.

And we say G'day.

Often, searching for documentation is so b****y annoying!!!

I should have mentioned where I found the information I posted, because I couldn't find it again.

It seems to be described in only one document, "iWay Server Administration for MVS and VM". The PDF document I downloaded is for v5.3.2 and I can't find this document online, nor any other that describes how to create a synonym using code.

I am hoping to find syntax to create synonyms with some kind of "replace" functionality, instead of first dropping then creating the synonym.

That "Enterprise Content Library" doesn't work very well - it did not help me find the correct documentation.

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

I just did a search my self, and found nothing useful.

So I went back to the tried and true method.

Turn trace on on the server and create a synonym with Overwrite Synonyms selected.

And

.
.
.
.
.

It created the command

.
.
.
.
.

CREATE SYNONYM {name} FOR {tblname} DBMS {dbtype} AT {connection} DROP

I was hoping you would pipe in!

Thank you for that syntax I'll give it a try.

Regards,

Well you are lucky.

Its morning, and my brain is still working.

This works very nicely! Thanks a lot.

Now to open a case complaining about the lack of documentation!

Cheers,

Good point.

I wonder how many other options there are for the command.

Could always do more tracing......

Francis/Waz,

What puzzles did you solve by creating/re-creating metadata on the fly using the CREATE SYNONYM command?

Thanks.

We have a ReportCaster job that is scheduled to generate metadata on a regular basis. The DB2 datamart changes monthly - new columns, new tables, column name changes, etc. Instead of relying on a WF Admin to manually regenerate the metadata from the WF Reporting Server Console, this job does it automatically.

We do it the same way. We recreate 99,9% of our MFD on a daily basis.

I did find two more options:

Dynamic Columns:
CREATE SYNONYM {name} FOR {tblname} DBMS {dbtype} AT {connection} NOCOLS

Does not create column definitions.

MFD for Stored Procedure:
CREATE SYNONYM {name} FOR {tblname} DBMS {dbtype} STOREDPROCEDURE AT {connection}

Kind regards
Markus

This message has been edited. Last edited by: Computix,

Markus, Thanks for the additional info.

I have been wondering how people use the "Dynamic Columns" synonyms. If no column information is created, the report developer must have some other method of determining column names.

True. A special type of master file is created that will cause DevStudio to first get the column names from the dbms before the field inspector is shown. I'm not very sure of how the server deals with this kind of master file, my guess is that it will just ship the request without checking and leave the checking to the dbms.

We found that when using dynamic columns, WebFocus sends an additional request to the DB-server in order to determine columns and datatypes before executing the real request.

We use then on our development and test WebFOCUS-system as a "fallback". We created those for all available tables, currently around 9000 MFDs (including Test, Beta and Production-DB).
Our final goal is to find out using Ressource Analyzer wether all accessed MFD are registered for deployment.

Regards,
Markus

Sorry Guys but I have a concern ...

What if I want to create a Synonym for a .txt file??? Which will be the syntax for that???

Thanks in advance
Regards

As the Create Synonym command will use and catalogs to find the columns formats, this will not work for a "flat" file.

You will have to build it yourself

If the txt file is a delimited file you can create a synonym using the Delimited Flat File adapter. It can use the header row if there is one for column names and examine the data itself to determine data types.

If the txt file is fixed format then you do have to build the synonym yourself, unless you happen to have a COBOL FD section that describes it.

Hi all ...

I did what Waz recommended about turning traces and checking the syntax, while creating a master file using the Web Console, so I found this ...

CREATE SYNONYM dwh_etl_ccn/archivo_pdpn FOR /apps/iway7703/ibi/apps/dwh_etl_ccn/&ARCH.EVAL DBMS DFIX PARMS ' DELIMITER "," HEADER NO' DROP

So it is possible to create a mas and acx from a text file using this command, and it works, the only thing is that the name of the file has to be ALL lower cases, if there is one upper case letter on the name of the file it won't work, unless you put the full path to the file ... I did not try the HEADER option but I think you can add it to the syntax and it'll still work.

Also as you can see the name of the file is a variable, since I need to create a dynamic master file for the name of the file that another process passes using the URL, now I have to create a DM Flow so it reads the file and process it, using again, the name of the file embedded on the URL ...

Thanks a lot for the hints !!!
Best Regards

After some struggling with syntax errors on this command I found that apparently it requires an END statement:

CREATE SYNONYM &APP/&MASTER
 FOR &CSVPATH
 DBMS DFIX PARMS 'DELIMITER  "," HEADER YES'
 DROP
END

I think you will find that if its coded on a single line, you don't need the END statement

I did try that and it kept throwing syntax errors at me. Until I added the END at the next line.

See here:

 CREATE SYNONYM xxx/test FOR D:\apps\ibi\apps\xxx\test.csv DBMS DFIX PARMS 'DELIMITER  "," HEADER YES' DROP
 -RUN
 (FOC1544) Syntax error: SYNONYM xxx/test FOR D:\apps\ibi\apps\xxx\test.csv
 DBMS DFIX PARMS 'DELIMITE
 Exiting due to Exit on Error...

 CREATE SYNONYM xxx/test FOR D:\apps\ibi\apps\xxx\test.csv DBMS DFIX PARMS 'DELIMITER  "," HEADER YES' DROP END
 -RUN
 (FOC1544) Syntax error: SYNONYM xxx/test FOR D:\apps\ibi\apps\xxx\test.csv
 DBMS DFIX PARMS 'DELIMITE

 CREATE SYNONYM xxx/test FOR D:\apps\ibi\apps\xxx\test.csv DBMS DFIX PARMS 'DELIMITER  "," HEADER YES' DROP
 END
 -RUN
 ?FF test
 FILENAME=  TEST
...

Yes you are right, I should have checked our own create synonym fex, it has the END and has been that way for many years.

quote:

I think you will find that if its coded on a single line, you don't need the END statement

Almost like the (not required) END with the JOIN command.

Is there an option to reformat fields for the CREATE SYNONYM command. The reason I ask is that we have a field formatted as "D20.2" and it prints with commas. I changed suppressed the commas by manually changing it to "D20c" (I didn't want the decimals either). And, that's OK for now. But, may not be forever. So, I'd like to be able to pass it something like this "FIELDX/D20c"

Let's bring this up once more...

I don't suppose anyone managed to trip over the hidden documentation for this command yet?

The reason I'm asking is that I need to figure out the syntax for creating a remote synonym on one of our OpenVMS RDB databases, where the default database has been set to something different than where 'my' table lives.

Normally I would do this using the webconsole, but alas, the webconsole is dead and there's nobody around to give it a proper kick.

Currently I have this:

SQL EDA SET SERVER OPENVMS_SERVER
-RUN

-REMOTE BEGIN

ENGINE SQLRDB SET SERVER FOODATA:[foowork]foo_bar

CREATE SYNONYM FOO_BAR/FOO_BAZOO FOR BAZOO DBMS RDB AT PRD DROP
END
-RUN

CHECK FILE FOO_BAZOO HOLD
TABLE FILE HOLD
PRINT *
END
-RUN

TABLE FILE FOO_BAZOO
PRINT *
WHERE RECORDLIMIT EQ 10;
END

-REMOTE END

And that code succeeds, so I thought I had it.
Next, I refreshed the local synonym on our Windows-server (this is a hub/sub setup) and attempted to query it, but alas, it gives me:

TABLE FILE FOO_BAZOO
PRINT *
WHERE RECORDLIMIT EQ 10;
END

x--------------
 (FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF)
 : %SQL-F-FLDNOTCRS, Column T1.BAZOO_DESC was not found in the tables in curre
 : nt scope
 L    (FOC1406) SQL OPEN CURSOR ERROR.  : FOO_BAZOO
 (FOC1400) SQLCODE IS 1400 (HEX: 00000578)
 : SQLCODE IS -1 (HEX: FFFFFFFF)
 (FOC1407) SQL FETCH CURSOR ERROR.  : FOO_BAZOO

The thing is, I went through this exercise because originally the (almost identical) error I got at the end was about a field that shouldn't have been in that master at all. That was a field that turned out to be from the identically named table in the default database, specified in our EDASPROF.PRF as:

ENGINE SQLRDB SET SERVER  $1$DGA101:[foowork]foo_foo.rdb

This time around, the fields are correct, but it looks like the master won't work without the ENGINE SQLRDB line to point it to the correct database. How do I fix that?

BTW, Identifiers have been anonymized

I should add (because you're probably going to suggest that) that removing the ENGINE SQLRDB line altogether does indeed create a master file, but that points to the identically named table in the default database and not to the particular table I need from this specific database.

Oh, and here's the contents of the remote access file:

 SEGNAME=FOO_BAZOO, 
   TABLENAME=FOO_BAZOO, 
   CONNECTION=PRD, 
   KEY=FOO_ID, $

This message has been edited. Last edited by: Wep5622,

When you performed your second TABLE FILE FOO_BAZOO did you include it between -REMOTE BEGIN and -REMOTE END ?

I haven't been able to locate documentation on the CREATE SYNONYM syntax because, I am guessing, that the command has increased in complexity over the years(?) and, of course, GUI!

If I were to guess where you might locate it, I would suggest looking at old adapter manuals (online) for the specific platform and RDBMS that you are after. As the additional syntax is so very different for each adapter, if I remember correctly, the syntax used to be within the specific adapter manual(s).

Good luck!!

T