AS/400 'SELECT 1' Equivalent

Question

I'm trying to help a friend troubleshoot connection problems with an AS/400. He'd like to run a query against the AS/400 that will always return a record, (similar to a SELECT 1 against MSSQL).

He's attempted running SELECT 1 but without luck ... I'm assuming that the AS/400 expects a FROM TableName ...

Can anyone provided assistance? Is there a system table/file that always exists that he can run a SELECT TOP 1 (or it's equivalent) against? I've never interacted with an AS/400 so I'm a bit out of my element ... was hoping SO could give me a quick answer ...

Thanks in advance!

The "...but without luck..." needs clarification. If the problem is indeed involving the connection, then "SELECT 1" should be good enough. An error for that would be returned from DB2 on the AS/400 if the connection is working properly. Otherwise the error will come from one of the connection protocols. — user2338816, Mar 24 '14 at 07:50

score 14 · Accepted Answer · answered Mar 16 '10 at 13:42

14

I'm pretty certain it's DB2 running on those boxes (they're called iSeries now, by the way) so you can give:

select * from sysibm.sysdummy1

a shot. This should return a single column IBMREQD, set to Y.

answered Mar 16 '10 at 13:42

paxdiablo

854,327
234
1,573
1,953

I'll pass this on ... Would 'SELECT * FROM sysibm.sysdummy1 FETCH FIRST 1 ROW ONLY' be valid? – cookbr Mar 16 '10 at 13:44
That's certainly valid syntax for DB2/z and DB2/LUW so I assume it would work on the iSeries as well. But I don't think it'll be needed, SYSDUMMY1 only *has* one row. – paxdiablo Mar 16 '10 at 13:46
Ah, I see :-) I'll pass this on and we'll see how it goes. Appreciate the response! – cookbr Mar 16 '10 at 13:48
And looking at the docs, 'fetch first n rows' was added in 5.1 back in 2001. – paxdiablo Mar 16 '10 at 13:49
See http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/db2/rbafzmstintsel.htm#intsel and remember: publib-boulder has a HUGE amount of online docs for all IBM products. That should be the first point of call if you're using IBM's wonderful software :-) – paxdiablo Mar 16 '10 at 13:53
Just tried that SQL in the answer on an actual AS/400, and it does work. No need to limit the number of rows returned, as it returns only one row as-is. – Brian Showalter Mar 16 '10 at 14:06
@paxdiablo Haven't heard back from my friend yet, but I'm sure what you've provided will work ... thanks for the answer and link! – cookbr Mar 17 '10 at 13:05
@Brian Showalter Thanks for confirming! – cookbr Mar 17 '10 at 13:06
"SELECT * FROM sysibm/sysdummy1 FETCH FIRST 1 ROW ONLY" works in STRSQL (note / instead of .) – Thorbjørn Ravn Andersen Aug 16 '10 at 09:57
(and they are named *IBM i* this week) – Thorbjørn Ravn Andersen Aug 16 '10 at 09:57

Mohamed · Answer 2 · 2018-07-06T21:05:28.787

1

table QSQPTABL can also be used for this.

edited Jul 06 '18 at 21:05

answered Feb 17 '14 at 22:55

Mohamed

11
4

Could you clarify why you think QSQPTABL is much more appropriate? – Buck Calabro Feb 17 '14 at 23:19
This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post - you can always comment on your own posts, and once you have sufficient [reputation](http://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](http://stackoverflow.com/help/privileges/comment). – Code Maverick Feb 18 '14 at 04:59
This table has just one row and one column. The same reason sysibm.sysdummy1 is relevant, the table i mentioned is also valid. – Mohamed Jul 06 '18 at 19:55
@BuckCalabro Could you please revoke the down voting on my post? please – Mohamed Aug 28 '18 at 20:16
Could you clarify the reasons one would use QSQPTABL instead of the industry standard? – Buck Calabro Aug 29 '18 at 23:38
How one defines what is industry standard. I had been using QSQPTABL for more than 15 years now. @BuckCalabro – Mohamed Aug 31 '18 at 16:10
Industry standard is what tens of thousands of people have been doing. QSQPTABL is a poor solution because it is not a documented interface. QSQPTABL is an internal mechanism for IBM to realise several different goals; see https://www-356.ibm.com/partnerworld/wps/servlet/download/DownloadServlet?id=3a7IE3UmCg3iPCA$cnt&attachmentName=improving_sql_procedure_performance.pdf&token=MTUzNTg4NDk2NDIyNQ==&locale=en_ALL_ZZ for example. Because QSQPTABL is an internal, undocumented interface, IBM can change it at any time without telling us. – Buck Calabro Sep 02 '18 at 10:47
1

SYSDUMMY1 is documented in many places, not only for IBM i but Db2 for z and LUW. Here is one IBM i document: https://www.ibm.com/developerworks/ibmi/library/i-use-sql-built-in-global-variables-trs/index.html – Buck Calabro Sep 02 '18 at 10:48

AS/400 'SELECT 1' Equivalent

2 Answers2