Editors: Ted Holt and Howard Arner Managing Editor: Mari Barrett
This issue of the OS/400 Edition of Midrange Guru is sponsored by:
Topics Covered In Volume 1, Number 1:
Hey Ted :
I have a PTF that is showing on my server as damaged. I have tried several ways to fix this, including re-downloading the PTF and using the Delete PTF (DLTPTF) command and the Remove Program Temporary Fix (RMVPTF) command, and I have been unsuccessful. Can you assist me with this problem?
I sure can. The PTF must be reloaded with the Load Program Temporary Fix (LODPTF) command and then the RMVPTF command can be used to remove it. Run LODPTF from the media that you used to originally load the PTF. If you no longer have that media, download the PTF again. Once the PTF has been loaded, remove it. If everything on your system appears to be running properly, then wait until after your next IPL to reload the PTF. The IPL will reset the pointers to the objects contained in the PTF. If you experience system problems, IPL as soon as possible and then reload the PTF. If you have a damaged PTF, you may also have damaged objects. You might want to run the Reclaim Storage (RCLSTG) command as soon as possible.
To unsubscribe, change your email address, or sign up for any of Guild Companies, Inc's free email newsletters, visit http://www.itjungle.com. Hit the SUBSCRIBE button on the homepage and it will lead you to our online subscription system.
When you sign up for one of our e-newsletters, you can be assured that your e-mail address will NEVER be sold to an outside company.
If you have a tough problem, our gurus can probably help. Their mailboxes are always open.
* Email Ted Holt at email@example.com
* Email Howard Arner at firstname.lastname@example.org
I just got a copy of Crystal Reports, and am really enjoying the product. However, one of the files in the QS36F library on my AS/400 doesn’t seem to work with Crystal. I can add the file to a report, but when I use fields from that file and attempt to preview the report, I get an SQL5001 error: column qualifier or table undefined. What should I do?
Always consult with a Midrange Guru, as you have wisely done.
Whenever you get an SQL5001 error, it is usually due to one of two reasons. In your case, I know it is not the first of these situations, but I'll include the information for review. If you get this error and see *N as the qualifier, check that you have a host database name declared on your system. You can check this by using the Work With Relational Database Directory Entries (WRKRDBDIRE) command. If you do not have an entry where the remote location reads *LOCAL, then you need to create one. Execute the Display Network Attributes (DSPNETA) command to get the value for your Default Local Location. This value is the name that you should use as your host database name. Next, execute the WRKRDBDIRE command to see the relational database directory entries. Use option 1 to add an entry using the name found in the DSPNETA command. Set the address of the system to *LOCAL and choose *SNA for the type. After IPL, you should no longer see *N at the front of your queries and the problem should be resolved.
The second reason for receiving this error is caused by special characters in the column or table names of your application. OS/400 allows you to use the dollar sign ($), the at symbol (@), the pound sign (#), and the period (.) in table and column names. However, the SQL specification only allows table and column names to be composed using letters, numbers, and the underscore (_) character. Crystal Reports is designed to the SQL specification, so it does not expect to see the OS/400-allowed characters in your table or column names. Crystal will generally get along with the $ and # characters, but it really doesn’t like the @ character in a column name. The only cure is to create an alias or view for the offending column or table, as shown in the following example:
In order to make this table friendly to Crystal, create an SQL view of the data. A view is like a logical file, except it contains no data. When you attempt to access the view, the underlying SQL statement is executed by an AS/400 or iSeries server and the data is returned to the application as if the data is in a physical file. The following example is a create view statement that makes the table Crystal-happy:
CREATE VIEW QS36F/MYPARTSVIEW AS
Once the above statement is executed, you will have a view called MYPARTSVIEW in your QS36F library that Crystal Reports and other SQL/ODBC/ADO-based products can access with impunity. You can execute the above statement in several ways:
* Use the Start SQL Interactive Session (STRSQL) command
* Place the statement in a source physical file and use the Run SQL Statement (RUNSQLSTM) command
* Use Client Access Express; or
* Use a tool like SQLThing from Client Server Development, available at http://www.sqlthing.com
The view will contain the columns PARTID, DESCRIPTION, and PART_PRICE. Also, note that SQL does not have the 10-character limitation on column names, so take the opportunity to create a view, as I demonstrated earlier, to rename the columns to a naming convention that will make more sense to your users.
For those of you unaware of Crystal Reports, it is available from the Crystal Decisions subsidiary of Seagate Technology at http://www.crystaldecisions.com.
One of the great things about the OS/400 community is that it is indeed a community. We may be all working from our cubicles, but we are all connected and trying to figure out how to best employ the computer technology at our disposal. There are more than a few ways to skin any cat, and if you have a clever and unique answer to a problem that one of our Midrange Gurus has solved, we'd love to hear from you. This newsletter is an open dialog, and we value your input as well as your readership.
It goes without saying--but we'll say it anyway--that your hard technical questions pertaining to real world problems are equally valuable as a foundation for this newsletter as are your programming insights. We hope you find all the editions of Midrange Guru valuable, and we are going to work hard to make sure that they are.
Copyright © 1996-2008 Guild Companies, Inc. All Rights Reserved. This document may be redistributed by email only in its unedited form. Midrange Guru is a registered trademark of Guild Companies, Inc. ISSN 1049-7757 AS/400, iSeries, and OS/400 are registered trademarks of International Business Machines Corp. All other product names are trademarked or copyrighted by their respective holders.