i/System i/iSeries/AS400

The EnterpriseOne i/System i/iSeries/AS400 category will include all posts and pages pertaining to the IBM i/System i/iSeries/AS400 when administering EnterpriseOne.

EnterpriseOne UBE Runtime Performance Audit For IBM iSeries

The following is a SQL query that I use to audit the performance of UBEs. Stick the results in a Excel spreadsheet and analyze to your hearts desire.

SELECT 
JCJOBQUE as JobQueue,
JCENHV as Environment,
JCUSER as User,
substr(jcfndfuf2,0,locate('_',jcfndfuf2) +
   locate('_',substr(JCFNDFUF2,posstr(jcfndfuf2,'_')+1))) as JobName,
   date(char(JCSBMDATE+1900000)) as SubmitDate,
   SUBSTR(RIGHT('0' || right(repeat('0',6) || JCSBMTIME,6), 6), 1, 2) || ':' ||
      SUBSTR(RIGHT(right(repeat('0',6) || JCSBMTIME,6), 4), 1, 2) || ':' ||
      RIGHT(right(repeat('0',6) || JCSBMTIME,6), 2) as SubmitTime,
   date(char(JCACTDATE+1900000)) as EndDate,
   SUBSTR(RIGHT('0' || right(repeat('0',6) || JCACTTIME,6), 6), 1, 2) || ':' ||
      SUBSTR(RIGHT(right(repeat('0',6) || JCACTTIME,6), 4), 1, 2) || ':' ||
      RIGHT(right(repeat('0',6) || JCACTTIME,6), 2) as EndTime,
timestampdiff(2,char(
   timestamp(date(char(JCACTDATE+1900000)),
      TIME(SUBSTR(RIGHT('0' || right(repeat('0',6) || JCACTTIME,6), 6), 1, 2) || ':' ||
         SUBSTR(RIGHT(right(repeat('0',6) || JCACTTIME,6), 4), 1, 2) || ':' ||
         RIGHT(right(repeat('0',6) || JCACTTIME,6), 2))
   )-
   timestamp(date(char(JCSBMDATE+1900000)),
      TIME(SUBSTR(RIGHT('0' || right(repeat('0',6) || JCSBMTIME,6), 6), 1, 2) || ':' ||
         SUBSTR(RIGHT(right(repeat('0',6) || JCSBMTIME,6), 4), 1, 2) || ':' ||
         RIGHT(right(repeat('0',6) || JCSBMTIME,6), 2))
   ))
) AS RUNTIME_SEC
FROM SVM812.F986110
WHERE JCSBMDATE>116121
ORDER BY 9 DESC

Here is the one for SQL Server.

Have fun!

EnterpriseOne IBM iSeries (AS400) Power Outage Fix

POWER OUTAGE IN THE DATA CENTER!

WHAT!?! That’s not supposed to happen.

Don’t I know it. Don’t ask.

After this unfortunate incident, EnterpirseOne came back up great. Nothing in the logs indicating an issue and no issues reported by users.

That is until several users logged in and the system came to a screeching halt. Grids wouldn’t populate. UBE jobs stayed in a “Waiting” status. And eventually, the following error was found in the enterprise server logs:

IPC2100017 – createIPCMsgq (name Net32Q) failed, errno=3457: File exists..

It was a very frustrating day, but thankfully we were able to resolve it using Oracle Support Doc 659250.1 :

Cause

Invalid files exist in system library.

UBEs on the iSeries process in USRQs (User Queues). When the UBE is done processing, the USRQ should be deleted by EnterpriseOne. On occasion, USRQs do not get deleted, leaving orphaned USRQs in the system. If another UBE is submitted to the server, it may try to use the orphaned USRQ causing the error message above. To resolve this issue, the orphaned USRQ(s) need to be deleted.

Solution

Steps to delete a USRQ:

  1. ENDNET to end oneworld services using AS400 user profile ONEWORLD
  2. CLRIPC
  3. Issue command: WRKOBJ E910SYS/Q0000* – where E910SYS is your E1 system library name
  4. If the object type is *USRQ you can delete these orphaned user queues.
  5. STRNET to start JDE services
  6. Start HTML server

 

IBM iSeries: Starting Printer Writer – STRPRTWTR

dinosaurThose of you that know me, understand that I know enough about the IBM iSeries to support EnterpriseOne, but some of the more base iSeries knowledge just hasn’t been absorbed. I think it’s because when I first started with OneWorld/EnterpriseOne I thought that the AS400 was a dinosaur and was ready to be put out to pasture.

Hey! I was young and didn’t know any better.

Child TechnologySince then, I have come to respect the IBM technology and understand that there is a time and place for anything.

So, today, I get a call from someone saying that her World reports were not printing. It’s Friday afternoon and the guy that I thought would know the most about this was out of the office. I remembered that I’ve done this a few times in the last 18 years, so I figured I could stumble through it a little and get it done.

After 10 minutes or so of trying to figure out the correct STRPRTWTR command, I humbled myself and talked to the other person I thought might be able to help me. He logged in and ran the correct STRPRTWTR command. It took a whole 30 seconds and the user was getting her 23 copies of the same report (she kept re-trying).

Sometimes, it’s just way quicker and less frustrating to ask first.

Thanks, Sannan.

Oh, by the way, the correct STRPRTWTR command is:

STRPRTWTR *ALL

IBM STRPRTWTR documentation: Start Printer Writer (STRPRTWTR)

Finding The Row Count For All Files In An iSeries Library And All Tables In An SQL Server Database

Ive been moving several EnterpriseOne databases to SQL Server lately. In order to do a quick check to make sure that all the data was transferred, I like to compare the number of rows on all the tables. Below are the SQL queries that I use to get a row count on all tables in a database and all files in a library:

iSeries SQL query to get a row count on all files in an iSeries library:

SELECT TABLE_SCHEMA, TABLE_NAME,NUMBER_ROWS
FROM QSYS2.SYSTABLESTAT
WHERE TABLE_SCHEMA = 'PRODDTA'
AND NUMBER_ROWS > 0
ORDER BY NUMBER_ROWS DESC, TABLE_NAME DESC

NOTE: the iSeries SQL query is easiest to do from System i Navigator because you can easily copy the records into Excel.

SQL Server SQL query to get a row count on all tables in an SQL Server database:

SELECT '[' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + ']' AS fulltable_name,
SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name,i.rows
FROM sys.tables AS t
INNER JOIN sys.sysindexes AS i ON t.object_id = i.id
AND i.indid > 2
WHERE i.rows > 0
ORDER BY i.rows DESC, t.name DESC

UPDATE: The following SQL statement will not only give you the row counts of all the tables in the SQL Server database it will also give you the amount of disk space that it is taking:

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8/1024 AS TotalSpaceMB, 
    SUM(a.used_pages) * 8/1024 AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8/1024 AS UnusedSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    TotalSpaceMB desc, t.Name 

Are there any methods that you use to do a quick data integrity check?

How To Get A Disk Usage Report From IBM iSeries

Below are the steps that I used to get a disk usage report from our IBM iSeries which we used to find and track the large files in our JDE EnterpriseOne data library:

  1. Type: GO DISKTASKS and press [Enter]. Then choose option 1 in the screen below:
    IBM GO DISKTASKS
  2. Choose option 1 for when to collect the information:
    IBM GO DISKTASKS
  3. On the next screen type in *CURRENT in both the Date and Time fields and press [Enter].
    IBM GO DISKTASKS
  4. Press [F12] twice to get out of the Collect Disk Space Information screen.
  5. Do a WRKACTJOB and find a job similar to the one highlighted below:
    IBM GO DISKTASKS
  6. The job may run for several hours depending on the configuration of your system.
  7. When the job above has finished, go to the Disk Space Tasks menu again by typing GO DISKTASKS. This time, choose option 2: Print disk space information.
  8. On the next screen choose Library by putting a 1 in the field as in the image below:
    IBM GO DISKTASKS
  9. Enter a Y in the next field to indicate that you want to include info about objects in libraries.  Then press [Enter].
  10.   Fill in the library name that you would like to get information about as in the image below:
    IBM GO DISKTASKS
  11.    Finally, find the report using System i Navigator and drag it to your desktop:
     IBM System i Navigator

I hope that helps you find the large objects in a library on an IBM iSeries.

Reference: IBM GO DISKTASKS Overview