AS400 Tutorials

Query/400 Keeps On Going

October 12th, 2009 by John
Respond

One of the most used tools for years and years has got to be hands down Query/400. It just keeps on working and is incredibly simple to use! Even better is that queries built using this tool work even through OS upgrades without any sort of issues.

Query/400 is completely menu driven, making it very easy to use and friendly from a user standpoint so you don’t have to learn any complicated syntax or SQL… although it can certainly help if you do know SQL.

To fire up Query/400 simply type in “WRKQRY” on a command line and you should be greated with a simple looking screen asking you to build a new query or modify an existing one.

wrkqry

Once you enter in the name of your query definitition you will be brough to a menu of options. Now for probably 90% of the queries you will write you will probably only ever use the options for:

Specify file selections
Select and sequence fields
Select records

definequery

This gives you a straight up query of one or more files with as many constraints as you can possibly think of. Sometimes you will add an option to sort by one or more fields.

One handy feature of Query is using the F5 key to run and preview the report. But keep in mind that when you run this option is can consume a lot of system resources especially if temporary indexes have to be built. The simple rule of thumb is the longer it takes for something to show up when pressing F5 the less often you should use it when building your queries.

previewreport

I like Query/400 *alot* and although I don’t write many permanent queries that are saved and used I find it an indespensible tool for quick and dirty reports to validate data or for looking up information. Even though Query/400 has been around for ages and ages it is still a great tool and because of its versatility I highly recommend you use it for all of your report building and data inquiry needs.

Tags: Comments Off

AS/400 Operations Navigator to the Rescue

July 27th, 2009 by John
Respond

There is a large misconception running about the user community these days that the AS/400 and iSeries platform are old antiquated machine as scarce as the dinosaurs and just as hard to use with its crummy looking text only interface commonly known as the green screen.

This is a major sticking point for business looking to buy an AS/400 and especially for some system administrators that have been groomed on a Windows based environment with point and click ease of use and a built in wizard to do just about everything.

But there is really good news for those people who absolutely hate the old fashioned green-screens that the AS/400 and iSeries platform is known for… there has been a tool available to make maintenance and administration tasks easier. That cool graphical user interface tool is called Operations Navigator, it comes along with your regular licenses so it doesn’t cost any additional money and it has been available now for many years.

That’s right I said that the operations navigator utility is a GUI you can use with your AS/400 and iSeries systems. With it you can maintain users, catalogue system patches and program temporary fixes, watch jobs, administer printer output queues and more.

You want to run an SQL query to delete some data sets or run a quick and dirty report and export the results into Excel? Operations navigator has you covered. Want to copy files from your computer over to the integrated file system on the AS/400? Operations navigator can do that too.

If you are into keeping a watchful eye on the health and performance data of your system, once configured, operations navigator will spit out great looking charts and graphs of important performance metrics.

Did you know that you can also download your reports as text or pdf files from your AS/400 or iSeries with operations navigator, this very feature alone can replace you dependency for expensive third party software packages you may currently be using.

One of the greatest features not otherwise available until operations navigator came around is the ability to diagnose SQL statements and present them in a graphical diagram with each of the steps the system used to execute it in very thorough detail.

There is also a whole feature set available that will collect performance data on your programs and files and record how often temporary indexes are built, you then simply go back and review this data after running a collection and it will recommend indexes to build that will improve the performance of your reports and queries dramatically.

Eventually you may find yourself having to perform some tasks in the command line environment, but as time moves on more and more of features are being built natively into operations navigator with each new release slowly removing the dependency on the green-screen. Who knows though, you may even become a convert like some of us old 400 folks and start to use the command line interface instead of the GUI.

Nonetheless oprations navigator has such a rich set of tools and its ease of use make it an excellent tool to administering your AS/400 and iSeries systems so it is definitely here to stay.

Tags:   · · · · Comments Off

AS400 Survey

July 1st, 2009 by John
Respond

What AS/400, iSeries or System i product do YOU need the most?

Click on the link below to take our quick survey:

Click Here to take survey

Tags: Comments Off

AS400 Report Mining

June 27th, 2009 by John
Respond

We all have data… data, data everywhere and we need it in a user friendly form that makes sense. Data drives business, so the less time you spend getting at what you need the more time you can focus on getting important things done.

This is where report mining comes in.

You may not have heard the term “report mining” before but essentially report mining is just taking your printable reports from your AS40 or iSeries and bringing them over to your PC into a program like Excel, then scrubbing them up a bit.

Maybee you need to calculate some additional fields or condense the data into an easy to read format. This is where report mining can really come through.

Getting data off of your AS400 to a PC used to be a major hassle back in the dark ages… in fatc people hated it so much that software vendors sprung up all over to accomplish this now routine task.

If you have the luxury of having a programmer on staff that can write reports all day long then your in luck. But some places can’t and hiring an outside consultant to write reports at $175 dollars an hour adds up quickly.

Another option is using the excellent tool you might have is Query/400. This allows you to write simple reports joining together data from one or more files. 

Unfortunatley Query has limited logic capabilities that are handled better by regular programmed reports, but for probably 80% of the reporting needed it will suffice.

Anyhow, back to report mining. If the data you want to get at is already a part of an existing report then you are in luck. No need to write a query since you can just mine that report to get what you want.

The process is really simple and takes only a few mouse clicks.

Using Operations Navigator you can simply browse the printer data on the system then drag and drop any report on your As400 or iSeries onto your desktop. The software then automatically converts it into a text file.

Now this works with reports that are still in an outq on the system. The best way to handle this is to have your spool files go to your own output queue that is not attached to a writer.

After copying the file over to your PC, simply open up that text file with the Excel data import wizard, add the column breaks, clean up the report headings and you are off to the races.

This really is a poor mans way to generate reports, but its really simple and you should already have all the tools to do it installed on your computer.

All you need is Operations Navigator (aka iSeries Navigator) and Excel.

If you need something more complex then look into the other options out there like writing reports using Query or the Excel Add-in.

Tags:   · · · Comments Off

5 Minute AS400 Tutorial

May 27th, 2009 by John
Respond

Three Essential AS/400 Tasks You Need To Know

The operating system that runs on an AS/400, iSeries, i5 is known as OS/400 or i5/OS and it is command and menu driven. At its heart everything tends to be a command and even the menus that you see use commands behind the scenes. All of these commands are known as control language

 as400-main-menu

 AS400 commands tend to be three letter abbreviations of two or more words put together. For example the word “work” is commonly used in several commands and is abbreviated as wrk. Printable output on an AS/400 is known as spool files and commands to work with those are abreviated as splf— ok so thats four letters but it is a rare exception.

So if we put together Work and Spool Files we ge the command “wrksplf” Work with Spool Files and it allows you to maintain your printable output that is still residing on the system. And by the way commands are entered on the command line at the bottom of menus and denoted with the “===>” symbol.

 To see a list of AS400 commands that have been catagorized into groups type in “go verb” on the command line. As you will see there are *alot* of commands. Ultimately you will probably only ever use 20% of them, and realistically even for day to day system administration less than that.

as400-verb-commands

You can also prompt any command by typing it in and pressing the F4 key on the keyboard. This prompting will display more detail about the command and provide all of the available parameters that go along with it.

Everything that runs on the AS400 is called a job. And all of the jobs run within their own areas known as subsystems. To see a list of all the jobs currently running on the system you will use the command wrkactjob which stands for Work with Active Jobs.

Without getting into a bunch of techno jargon, subsystems are a way to run jobs that jobs can be allocated system resources like memory and CPU processing time. For example printers run in there very own subsystem called QSPL, which tends to have its own pool of memory allocated to it. That way others jobs in the system don’t use that allocated memory and vice versa. The memory can be reallocated easily if need be but that will be covered in another topic.

Out of the box an AS400 will have adequate subsystems configured to get you up and going… as time goes on your can create more subsystems or change the pre-configured ones if need be.

Lastly let’s take a look at how to shutdown or restart an AS400. Starting up an AS400 or restarting is know as an Initial Program Load or IPL for short. This is a rather straight forward process but can take quite a bit of time to complete.

as400-ipl-menu

In a very brief nutshell upon startup the system checks everything out and loads up the operating system and related systems to make the system opertational. During an IPL is most often when you will experience a hardware failure… not to worry though because most hardware failures that occur on an AS400 can be replaced while the machine is running. This includes hard disks, redundant power supplies and even memory.

as400-pwrdwnsys-command

So to shutdown or IPL a system you will use the Power menu which you access by typing in “go power” on the command line or alternatively using the Power Down System or pwrdwnsys command. Most people opt for using pwrdwnsys.

Tags:   · · · · 4 Comments

AS400 Packed Data Conversion

May 26th, 2009 by John
Respond

Why Your AS/400 Data Looks Like Guacamole…

Sometimes when people transfer data from an AS/400 to Excel or another program they run into a some data that looks a bit like guacamole, this tends to happen when transferring numerical data.

Without getting into a lot of boring technical details “packed data fields” are the default standard for storing numerical data on AS/400s in a format known as EBCDIC. PC based computers use ASCII and so they don’t always translate across when transferred in there native formats.

The problem is when you bring that data over to the PC world it doesn’t always translate correctly to ASCII… now this only tends to occur when using the data transfer program built into Client Access and not when using an ODBC connection or other program which does the data translation for you behind the scenes.

The symptom is that when you download the data you will see letters in fields where there should be numbers from 0 through 9. To correct you simply translate these letters or characters over to the corresponding number as follows:

0 = ‘}’
1 = ‘J’
2 = ‘K’
3 = ‘L’
4 = ‘M’
5 = ‘N’
6 = ‘O’
7 = ‘P’
8 = ‘Q’
9 = ‘R’

Now thats about as easy as duck soup… but it sure can be a pain. Ultimately to “cure” this issue you will want to get at the data another way and not use the AS/400 Data Transfer portion of Client Access for this specific table, instead use ODBC and SQL query, import it as part of a printable report, etc.

Tags:   · · Comments Off

Download Data From Your AS400 To Excel

May 22nd, 2009 by John
Respond

What The Software Vendors Don’t Want You To Know…

Everyone wants to get there data into Excel or spreadsheet of choice to slice dice and do whatever else you need to in a user friendly, powerful program tailor made for data manipulation and reporting.

Getting data from your AS/400 to Excel used to be a major pain but now its simple and routine process depending on what your reporting needs are. Simply replicating a table or pulling in a printed report can be done but if you need more complex functions or selecting slices of data from a large table you will want to get some third party software which will make this process easy.

Without the use of expensive third party software there are essentially two ways to pull data from an AS/400 over into your Excel spreadsheets.

The first method utilizes the data connection wizard built into Excel and an ODBC driver to query the database tables. Yes, Excel has a feature built right into it for querying databases (not just your AS/400) and storing the resulting data sets in your spreadsheets!

The AS/400 and iSeries ODBC driver comes with Client Access and is available as an option when you install Client Access on your PC. Alternatively there are some other vendors out there that sell ODBC or OLE drivers for connecting to your AS/400.

excel-database-connection

Utilizing the AS/400 ODBC driver you then use the Excel Data Wizard to open a connection to your system and pull down data. Alternatively you can use an Access database and create linked tables (again utilizing the ODBC driver) and then query and report against them.

excel-import-data

After using Excels data connection wizard (as shown above) you simply just import the AS/400 data into your desired spreadsheet page using the data import option which will then connect to your AS/400 ODBC data connection and pull down the data tables.

The default for this type of connection is to download the _entire_ database table… unless you want this there are some additional options you can use to add query constraints to pare down the data selection.

The second method is to take an existing spool file report, download it as a text file using iSeries Navigator and then importing it right into Excel. Excel handles this perfectly except you have to add in the column breaks and then cleanup the page headings.

import-as400-data

When using the Excel Text Import wizard to import AS/400 data contained in spool files make sure you select fixed width and not delimited field data. The alignment of the columns may take a little adjusting.

Tags:   · · · Comments Off