What is the AS400 Command to Run a Record Count on a Table?

matrix0511 used Ask the Experts™
I just need to run a simple record count on a table on an AS400. I'm running the command:

select count (*) from crpdta.f551201z

But that command is failing. I know the file exists. So I just want to make sure I"m stating the command correctly.

Can someone let me know real quick for me?

See below.

Thanks!RRecord Count
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Your command is correct... SQL is complaining about the table name... It is either mistyped or you are not connected to the proper SQL server to access that table.
your command looks fine
are you sure you don't have a typo in the object name?


Well, I can open the table from the application (JD Edwards E1). See below. It opens and I can even pull data. And the "Business Data - CRP" refers to the schema owner: CRPDTA which I stated in the command. NOt sure what else to check.

Application View
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Software Developer / Database Administrator
The problem is that you've specified *SYS naming convention instead of *SQL. Try changing the "dot" to a "slash".

e.g. intead of:

select count(*) from MySchema.MyTable


select count(*) from MySchema/MyTable



@davelash, that was it! You DA Man!! :-)


Excellent work Dave!!
The problem is that you've specified *SYS naming convention instead of *SQL.

To expand just a little, the default on the STRSQL command is *SYS. You can prompt the command and change it to *SQL when you run the command. (Or simply type the command with the parm on the command line.)

Or, once you're already in an interactive SQL session, you can press <F13> to access session services. Option 1, 'Change session attributes', lets you switch naming convention among other things.

The "Naming convention" can be involved in just about every SQL interaction that you'll have with an AS/400. Usually, whichever client you're using will default according to the platform the client is running on. And every client that I've seen will allow you to change to the alternative mode. Keep it in mind.


Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial