Most SQL Server developers know the benefits that linked servers provide. Available since SQL Server 7.0, linked servers can use almost any ODBC or OLE DB data source to provide read/write connectivity to remote database objects. This provides developers with a powerful, easy-to-use tool for quickly writing code that treats remote database objects as though they are part of the local database.
If you need to catch up on all that linked servers can do—and learn how to create one—see the references at the end of this tip.
For review, consider this example that uses a linked server (using ODBC or OLE DB) named DB2SERVER to update remote table PURCHLINE on an iSeries from within T-SQL code:
SET RCVQTY=@QTY, RCVDATE=@DATE
WHERE PO=@PO
AND POITEM=@POITEM
This Update is fairly typical in SQL with the exception that a four-part table name is employed. Specifically, for the iSeries using the IBM ODBC or OLE DB providers (IBMDA400 and IBMDASQL), the four-part table name is required as follows:
Note that the relational database name, schema name, and table names must be all capitals!
As I explained in an earlier article, SQL Server 2005 can avoid using the four-part syntax in many cases by using the OPENQUERY function and the new EXEC AT statement. However, there are a few instances when the four-part table name syntax may be required to facilitate ease of writing code. One example would be writing an Update or Delete against data in a remote table that requires a subquery or join from a local SQL Server table. In this case, the four-part table name provides an easy way to include local and remote tables in a single statement:
SET RCVQTY=@QTY, RCVDATE=@DATE
FROM DB2SERVER.S105X6HM.DATALIB.PURCHLINE AS PURCHLINE
JOIN PurchaseReceipts On
PurchaseReceipts.PONum=PurchLine.PO And
PurchaseReceipts.ItemNum=PurchLine.POItem And
PurchaseReceipts.ReceiptDate=Convert(VarChar(10),GetDate(),101)
This is very powerful integration functionality, but one drawback to this technique is that the linked server, relational database, and schema names all have to be hard-coded. Every time this remote table is referenced, these other three pieces of information go along with it. If this table is referenced in many T-SQL procedures or scripts, you'll have quite a maintenance chore if any of these three additional pieces of information change.
To help reduce this chore, SQL Server 2005 has introduced the concept of a "synonym." Somewhat similar to a DB2 alias name, the synonym simply provides an alternative name for referencing a remote table. (Actually, the synonym can be used to provide alternative names for other SQL Server objects as well. See the online help for more information.)
Using the familiar SQL CREATE/DROP syntax, a synonym is created or dropped as follows:
DROP SYNONYM DB2PurchaseLine;
The messy reference to DB2SERVER.S105X6HM.DATALIB.PURCHLINE can now be reduced to DB2PurchaseLine. With the creation of the above synonym, the original Update statement is reduced to the following:
SET RCVQTY=@QTY, RCVDATE=@DATE
WHERE PO=@PO
AND POITEM=@POITEM
The more complex sample statement is simplified as well:
UPDATE PURCHLINE
SET RCVQTY=PurchaseReceipts.Qty,
RCVDATE=PurchaseReceipts.ReceiptDate
JOIN PurchaseReceipts On
PurchaseReceipts.PONum=PurchLine.PO And
PurchaseReceipts.ItemNum=PurchLine.POItem And
PurchaseReceipts.ReceiptDate=Convert(VarChar(10),GetDate(),101)
Now, if you upgrade your iSeries/i5 and the relational database name of the system changes, you'll only have to DROP and CREATE each synonym instead of hunting through scripts, functions, and procedures. (ALTER SYNONYM doesn't appear to be available.)
Note that a synonym definition is created at the database level, not the server level. If you need to reuse a synonym among several databases, you may want to consider putting the synonym in a special "shared" database.
Once a synonym is created, it can be located under the Synonyms node of the SQL Server tree within the SQL Server management console. As with other objects, use the GRANT statement to assign permissions to appropriate users.
SQL Server 2000 and SQL Server 7.0 users can achieve the same thing using CREATE VIEW. The biggest difference between the techniques is that the VIEW definition is evaluated at creation time, whereas the synonym is created on the fly. If the remote table underlying the view changes, then the view may need to be re-created, whereas the synonym responds to changes on the fly.
In summary, synonyms make code cleaner by avoiding the use of the ugly four-part object name, and they ease maintenance chores by changing the reference to the linked server name, DB2 relational database name, or schema (library) name in one place.
As long as you're taking advantage of the rich integration features with linked servers, save yourself a few headaches and package your long remote-object references as synonyms.
References:
"DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements"
"Running Distributed Queries with SQL/400 and SQL Server 7.0" (AS/400 Network Expert, September/October 2000)
"Patch Those Leaky Interfaces"
More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.
TRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now! Request Now.
Forms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.
Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:
Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.
IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn:
LATEST COMMENTS
MC Press Online