Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Metadata generation using the CREATE SYNONYM command
Page 1 2 

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Metadata generation using the CREATE SYNONYM command
 Login/Join
 
Expert
posted
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,


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Expert
posted Hide Post
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!


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Expert
posted Hide Post
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...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Expert
posted Hide Post
Waz,

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

Cheers,


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Expert
posted Hide Post
I guess it's morning for you.

My brother is temporarily somewhere in Australia, say hello!


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Expert
posted Hide Post
That may be difficult has its a big place.

And we say G'day.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Expert
posted Hide Post
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,


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Expert
posted Hide Post
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

Big Grin


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Expert
posted Hide Post
I was hoping you would pipe in!

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

Regards,


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Expert
posted Hide Post
Well you are lucky.

Its morning, and my brain is still working.

Smiler


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Expert
posted Hide Post
This works very nicely! Thanks a lot.

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

Cheers,


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Expert
posted Hide Post
Good point.

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

Could always do more tracing...... Music


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Platinum Member
posted Hide Post
Francis/Waz,

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

Thanks.



In FOCUS since 1985 - WF 8.009/8.104 Win 8 Outputs: ALL of 'em! Adapters: Sql Server Teradata Oracle
 
Posts: 161 | Location: Dallas, TX | Registered: February 20, 2009Report This Post
Expert
posted Hide Post
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.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Silver Member
posted Hide Post
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,


WF 7.6.6 (MRE,BID, DevStudio, partly RC) on Windows 2003 /Apache/Tomcat
Output: HTML,Excel,PDF,PPT
Adapters: SQL Server, DB2, Oracle
 
Posts: 38 | Location: Austria, Linz | Registered: June 19, 2009Report This Post
Expert
posted Hide Post
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.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Virtuoso
posted Hide Post
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.


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Silver Member
posted Hide Post
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


WF 7.6.6 (MRE,BID, DevStudio, partly RC) on Windows 2003 /Apache/Tomcat
Output: HTML,Excel,PDF,PPT
Adapters: SQL Server, DB2, Oracle
 
Posts: 38 | Location: Austria, Linz | Registered: June 19, 2009Report This Post
Member
posted Hide Post
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


WebFOCUS 8.0.0.8
Linux
HTML, PDF, PPS, EXCEL, AHTML, FLEX, JSCHART
 
Posts: 8 | Location: Mexico, City | Registered: April 27, 2012Report This Post
Expert
posted Hide Post
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


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Guru
posted Hide Post
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.


N/A
 
Posts: 397 | Location: New York City | Registered: May 03, 2007Report This Post
Member
posted Hide Post
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


WebFOCUS 8.0.0.8
Linux
HTML, PDF, PPS, EXCEL, AHTML, FLEX, JSCHART
 
Posts: 8 | Location: Mexico, City | Registered: April 27, 2012Report This Post
Virtuoso
posted Hide Post
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


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Expert
posted Hide Post
I think you will find that if its coded on a single line, you don't need the END statement


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Virtuoso
posted Hide Post
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
...



WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Expert
posted Hide Post
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.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Expert
posted Hide Post
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"




   In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
   Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Virtuoso
posted Hide Post
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


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Virtuoso
posted Hide Post
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,


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Expert
posted Hide Post
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



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
  Powered by Social Strata Page 1 2  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Metadata generation using the CREATE SYNONYM command

Copyright © 1996-2020 Information Builders