MSSQL's openquery Saved Me
Sorry I've been quiet for the past few days. My Flex adventures took a quick detour through the massive land of our AS400 and DB2. Now that I had a prototype of my pretty line, bar, and pie charts I needed some real data. My job was to write a process to fetch our sales data from the AS400 server here at work nightly and populate some SQL Server tables with it. Easy Peasy, I thought. I didn't know what I was in for.
The first hurdle out of the gate was learning the structure of the files I was dealing with. (Apparently "tables" are called "files" in AS400 speak) I've been quite spoiled with extremely long and (mostly) descriptive table names in the SQL Server world. On the '400 however, file names are limited to only 10 characters. This leads to VERY creative abbreviations which make perfect sense to all the guys who have worked here for 10-20 years. :)
Once I had the data figured out, I was on my way again. We have a linked server set up in MS SQL 2000 which connects to the database on the '400 as a standard ODBC data source. My queries were running a little less than fast. In fact, they were dog slow. It would take anywhere from 20 to 30 minutes to return about 1000 records from 8 tables!
I started digging through my execution plans and was horrified to find that SQL Server was bringing over the ENTIRE contents of each table and performing all the joins and where clauses locally. That was over 9 Million records!
To prove it, I tested a simple select like:
Sure enough, the entire contents of the table (millions) would get transferred over the network (taking 5-10 minutes), and THEN the where clause would get applied and the handful of records I wanted where returned.
This tech note from Microsoft discusses the many reasons why SQL Server may not trust a remote data source to do its own processing. This ranges from data types in use, having a top clause, and compatible collations.
My solution was simple and effective. It was the openquery() function that SQL Server supports. It "executes the specified pass-through query on the specified linked server." That basically means I can specify the AS400 linked server and directly pass it a string of SQL for it to process and hand back the results. Now, this SQL string must be SQL in the native dialect of your linked server. For instance, I have to pass a DB2 formatted query. Furthermore, the openquery() function has a really annoying "feature" of NOT accepting variables for an input. I'm not sure what Microsoft programmer fell asleep at his desk the day he was supposed to program that part. Regardless, this is what the code looks like now:
2 FROM OPENQUERY(as400, '
3 SELECT *
4 FROM table
5 WHERE column = ''value''
6 ')
Biggest downside-- there is no code formatting to the SQL being passed in but hey, it's just a string that doesn't mean any thing to SQL Server, right? Biggest benefit-- it runs about 300x faster! This is because the remote server is doing all the joining, indexes and filtering and only passing back what matters to SQL Server.
The COBOL programmers could tell you the name of any "file" off the top of their heads, as well as what it stored: "What's ZDTPW03C? Oh come on, the system wouldn't work without ZDTPW03C."
I'm sending this to some of my former coworkers. There are a couple of processes that require data from DB2 to be ported over to SQL Server and they take forever.
The driver can be found in the Java/JTOpen Toolbox - http://sourceforge.net/projects/jt400
For AS400 running on the iSeries, I used the jt400Native.jar for the JDBC driver. You can then write sql statements in the form of: SELECT columns FROM library.file
We also wrote some programs with RPGLESQL and registered them as external stored procedures in the iSeries Navigator and used those from ColdFusion too.
And, in the end, it was all for a Flex application.
I'm assusming there's enough interest in this stuff and I'll post an article on it next week.
EXEC sp_executesql @sqlStmt
We've been able to write queries against library files and also been able to execute stored procedures written in RPGLESQL (which included some heavy business logic we didn't want in our flex app or on the CF side). You're right about the column and file names, they're cryptic.
It all runs blazingly fast too.
@Dutch: That article sounds great. Let us know when it is finished.
currently i'm using it to interface with peoplesoft. word of advice, you might want to think about containing your openquery statements in a view. it make it alot easier when you have to inserts or updates.
I have several posts on my blog about using JTOpen to connect to to db2/400 from ColdFusion that you might find useful. If you need/want help getting the driver setup (it's super simple), let me know.
While the linked server in SQL Server is an option, I prefer not to have to make copies of data when I don't need to.
http://www.brooks-bilson.com/blogs/rob/index.cfm?m...
@Rob: I'm with you on not wanting to have extra copies of the data. Unfortunately, my hands are kinda' tied on that since I don't have any control over the AS400 here at work. I think their main concern may be the performance load implications of tying the website directly to 400, but I'm not sure. A lot of it may just be related to the "this is how we've always done it" kind of thing.
Thanks for the link.
declare @myvar varchar(8)
set @myvar = 'value'
SELECT *
FROM OPENQUERY(as400, '
SELECT *
FROM table
WHERE column = ''' + @myvar + '''
')
Using the extra ' (single quote) before and after the variable.
declare @sql as varchar(200)
set @sql = '
SELECT *
FROM OPENQUERY(as400, ''
SELECT *
FROM table
'')'
exec(@sql)
@dattac: The method you showed does not work. openquery appears to not accept variables OR concatenations of variables and strings. In fact, it doesn't even allow for two strings concatenated together.
declare @myvar varchar(8), @sql varchar(2000)
set @myvar = 'value'
set @sql = 'SELECT *
FROM OPENQUERY(as400, ''
SELECT *
FROM table
WHERE column = ''''' + @myvar + '''''
'')'
EXEC(@SQL)
Or something similar to that (I'll have to check on it when I get home, it's been a while since I have used it). Again, just need to be careful with escaping all the single quotes.
Yep it will, at least for oracle and peoplesoft. i guess it depends if the database you're connecting to supports it.
It sped up my query by 1000 percent.
Thank you, Thank you, Thank you...
Please help,
Thank you,
Adeel Shafqat
As far as what the syntax *should* be, just look to Microsoft's MSDN pages:
http://msdn.microsoft.com/en-us/library/ms188427.a...
INSERT OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles')
VALUES ('NewTitle');
Good Luck!