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!

Babara

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. – Cryptogam

Answer 1

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.

Lienlienhard

I'll pass this on ... Would 'SELECT * FROM sysibm.sysdummy1 FETCH FIRST 1 ROW ONLY' be valid? – Babara

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. – Lienlienhard

Ah, I see :-) I'll pass this on and we'll see how it goes. Appreciate the response! – Babara

And looking at the docs, 'fetch first n rows' was added in 5.1 back in 2001. – Lienlienhard

See publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/db2/… 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 :-) – Lienlienhard

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. – Hereditable

@Lienlienhard Haven't heard back from my friend yet, but I'm sure what you've provided will work ... thanks for the answer and link! – Babara

@Brian Showalter Thanks for confirming! – Babara

"SELECT * FROM sysibm/sysdummy1 FETCH FIRST 1 ROW ONLY" works in STRSQL (note / instead of .) – Ursine

(and they are named IBM i this week) – Ursine

Answer 2

table QSQPTABL can also be used for this.

Geanticlinal

Could you clarify why you think QSQPTABL is much more appropriate? – Iou

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 you will be able to comment on any post. – Centring

This table has just one row and one column. The same reason sysibm.sysdummy1 is relevant, the table i mentioned is also valid. – Geanticlinal

@BuckCalabro Could you please revoke the down voting on my post? please – Geanticlinal

Could you clarify the reasons one would use QSQPTABL instead of the industry standard? – Iou

How one defines what is industry standard. I had been using QSQPTABL for more than 15 years now. @BuckCalabro – Geanticlinal

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 www-356.ibm.com/partnerworld/wps/servlet/download/… for example. Because QSQPTABL is an internal, undocumented interface, IBM can change it at any time without telling us. – Iou

SYSDUMMY1 is documented in many places, not only for IBM i but Db2 for z and LUW. Here is one IBM i document: ibm.com/developerworks/ibmi/library/… – Iou