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.

Quick Tip: How To Find OS Version On IBM i

To find the OS version of the IBM i (iSeries, AS/400), use the commands below:

DSPSFWRSC

F11

The above commands will result in a screen similar to the one below:

IBM i Version

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

I’ve 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?