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
September 27, 2010, 04:47 PM
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!
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
September 27, 2010, 05:43 PM
Waz
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.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
September 27, 2010, 06:02 PM
Francis Mariani
Waz,
Maybe you now can remove the "AT connection-name"
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
September 27, 2010, 06:03 PM
Francis Mariani
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
September 27, 2010, 06:10 PM
Waz
That may be difficult has its a big place.
And we say G'day.
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
November 04, 2010, 10:48 AM
Francis Mariani
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
November 04, 2010, 05:20 PM
Waz
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
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
November 04, 2010, 05:32 PM
Francis Mariani
I was hoping you would pipe in!
Thank you for that syntax 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
November 04, 2010, 05:45 PM
Waz
Well you are lucky.
Its morning, and my brain is still working.
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
November 08, 2010, 07:20 PM
Francis Mariani
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
November 08, 2010, 07:28 PM
Waz
Good point.
I wonder how many other options there are for the command.
Could always do more tracing......
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
November 09, 2010, 09:11 AM
DavSmith
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
November 09, 2010, 10:11 AM
Francis Mariani
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
November 15, 2010, 05:10 AM
Computix
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 MarkusThis 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
November 15, 2010, 01:10 PM
Francis Mariani
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
November 15, 2010, 01:48 PM
GamP
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
November 17, 2010, 02:30 AM
Computix
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
May 24, 2012, 07:44 PM
Jose A Pineda
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
May 24, 2012, 07:59 PM
Waz
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.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
May 25, 2012, 08:55 AM
Clif
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
May 25, 2012, 10:50 AM
Jose A Pineda
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
June 21, 2012, 05:30 AM
Wep5622
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 :
June 21, 2012, 05:24 PM
Waz
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.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
June 22, 2012, 03:41 AM
Wep5622
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 :
June 24, 2012, 05:36 PM
Waz
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.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
August 02, 2012, 05:26 PM
Doug
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
July 13, 2015, 10:51 AM
Wep5622
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 :
July 13, 2015, 10:53 AM
Wep5622
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:
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 :
July 13, 2015, 12:08 PM
Tony A
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