AS/400 commands for getting row counts for all tables in a library

first we create a table that is going to house the results.

create table jd7333.tablecounts (tablename char(10) not null, rowcount integer) ;

Now we execute this command to get the statements that will generate the insert statements and do the counts. 

select distinct  'insert into jd7333.tablecounts (select ' || '''' || table_name || '''' || ', count(1) from LIBNAME.' || table_name || ');'  from qsys2.systables where table_schema = 'LIBNAME' and table_type <> 'L' and not exists (select 1 from jd7333.tablecounts where tablename = table_name);

job done, you can restart it ay anytime and it won’t do them all again.

JDE EnterpriseOne Archiving–the devil is in the detail

There are a few competing archive products for E1 archival on the market at the moment and choosing the right one can be difficult.  Some companies have put together a small ESU and some basic “audit trail” technology over the top – but how useful is this data?  How easy is it to report from?  How quickly can you view the data once it’s been archived? 

With all archive products that I know, it’s difficult to get the data back.  You can’t just log into JDE and request the data to come back and wait a while.  The data needs IT intervention to get it back, or you need to write BI reports in third party BI utilities (via bespoke ODBC drivers) to view the historical data – which is going to cost more in the long run.

Remember that the Myriad solution gives you a NO COST & familiar interface back into your historical data – namely JDE!  Run an integrity, run any form in JDE you can do so on your historical data.  NO other solution offers you this flexibility.  You just sign into another environment and see your historical data.  Myriad can even create an environment where you see your historical data and your current data in the one environment!  Sure it might be a little slower than production – especially when you’ve reduced the size of your active data set by 50%! (this is a common value for data size reductions with archiving).

The Myriad solution is a consulting based solution, not a software based solution.  It’s backed up with IP and experience of having done it before.  It’s proven.  It will make your production environment go faster.  You will get an architected solution that is best for you and your needs.

Get in contact and we can explain the process in more detail.

Using all your existing hardware and software (or augment if you want), you can have complete access to all your JDE data – but your transactional environment will be running without the burden of history!  You will no longer have table scans that bring the system to it’s knees, because the data is not going to be in your production environment. JDE data archiving made simple, any platform, any database.

Missing machines in machine identification–F9654

This application gives a number of issues regarding missing machines.  Too often I go to a site and see problems with this app.  Quite often it’s related to values in DLPARLOC field in F9654 – but not for me in this instance.

My problem was that (it took me a while to work out) was that the user I was using (JDE) had language ‘E’ associated to it.  So the P9654A application was checking the F0005A table for H96 US values (i.e. machine categories)…

As soon as I made the JDE user use the domestic language, the problem went away and all my machines appeared!

Basics due to lack of blogging–ESU history correction

I thought that I’d record some CNC101 stuff, as I’m finishing up some documentation and thought I’d share some of the basics.

Post installation I think that it’s a great idea to take a snapshot of the system configuration by using "SupportAssistant”, this records a bunch of details about the system that you might want to refer to, once you’ve handed it over to the client.  It’s a helpful and useful utility.  I record a “generic issue” profile from the deployment server as a matter of course and then save the .gss file to my clients directory.

Get it from https://support.oracle.com patches and updates, jdedwards

Then choose the “Support Applications” menu item and search.

Next time I get asked about OCM’s or Central Objects counts – I can refer back to my SA file.  It’s also great for creating documentation for the site.  One thing it highlights is consistency for ESU application.  This MUST be right.  You cannot leave pathcodes looking inconsistent at a site, everything must be left neat and tidy and equal.  I’m referring here to the common practice of duplicating pathcodes.  This is done easily manually – but you MUST ensure that you fix the planner database to reflect that the ESU’s applied to your pathcodes are the same.

Firstly, if you are using OEE for the local database on the deployment server (you should be!), just use sqlplusw from the command line.  jde jde and e1local will get you a command prompt.

So now, I need to work out why TR looks like no ESUs (or ASUs) for that matter have been applied.   I do actually know why, as I did not copy the history when I copied the pathcode, but I need to work out how I can re-apply the history for when I need to run ESU’s against TR.

I enabled jde logging on the dep server ini file and then went to the ASU screen.

The following SQL reveals how JDE populates the grid:

SELECT  *  FROM JDESY900.F9671  WHERE  ( SDPKGNAME = 'UL2' AND SDSUDET = '90' )

SDPKGNAME  SD SD   SDSUDATE   SDSUTIME SD SDSUDFUT2   SDSUDFUT3 SDUSER     SDPID          SDUPMJ     SDUPMT
---------- -- -- ---------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---
UL2        90 15     111082     161018    PS900               0 JDE        P9670          111082     161018
UL2        90 15     111067     101629    PY900               0 JDE        P9670          111067     101629

There is a line per pathcode. 

I can simply duplicate this for the missing ESU’s and problem solved.

create table F9671tmp as SELECT  *  FROM JDESY900.F9671  WHERE  ( SDPKGNAME = 'UL2' ) ;

See that I’m missing PD and TR from the list

Execute the following:

update f9671tmp set sdsudfut2 = 'PD900' where sdsudfut2 = 'PS900' ;

update f9671tmp set sdsudfut2 = 'TR900' where sdsudfut2 = 'PY900'
insert into JDESY900.F9671 select * from F9671tmp ;

2 rows created.

SQL> commit ;

Commit complete.

Then you see the proper pathcodes from the ASU screen.

Now for the big one.  I have > 800 ESU’s that I need to do the same for…  but the work is the same effort for 800 as it was for 1 ASU.

  1* create table F9671tmp as select * from JDESY900.F9671  WHERE  ( sdsudfut2 = 'PY900' )
SQL> /

Table created.

SQL> update f9671tmp set sdsudfut2 = 'TR900' ;

3363 rows updated.

SQL> insert into JDESY900.F9671 select * from  F9671tmp where SDPKGNAME like 'JL%' ;

3355 rows created.

SQL> commit ;

Commit complete.

SQL> drop table F9671tmp ;

Table dropped.

So now, it looks like all ESU’s have been applied to TR900 also – great!