SQLServer table sizes and myArchive–native JDE data archival

This sort of information is priceless for comparative purposes.  This shows you a large installation of JDE and the size in rows and indexes and data for some of the largest tables.  The site will remain anonymous, as that is not important.  The relative size of the data is.

 

Table	Description	Rows	Reserved (MB)	Used Data (MB)	Used Index (MB)
F0911	Account Ledger	33271646	97354.38	32459.38	64810.5
F42199	S.O. Detail Ledger File	22424807	49727.8	45365.96	3923.2
F42119	Sales Order History File	6241321	22161.11	12260.99	9895.19
F4074	Price Adjustment Ledger File	27626864	20234.54	15417.78	4816.63
F49219	Sales Order Detail - Tag History File	24002854	18827.85	18752.41	75.18
F43199	P.O. Detail Ledger File - Flexible Version	9110611	18126.79	14235.55	3888.71
F4111	Item Ledger File	10363396	16472.47	8095.88	8374.5
F03B21	A/R Notification History Detail	23782360	14720.53	13271.45	1448.66

 

So in SQLServer a 100GB F0911 is about 35 million rows.

What else does this information tell me?

Of the 570GB of data in production 260GB (about half) is in the 8 tables above!

All these tables are susceptible to table scans.  All data above needs to be backed up and restored for copies of production.  This data spread is ripe for myArchive.  myArchive is the myriad JDE archiving solution. 

Some quick facts about myArchive

•Access your archive data using JDE screens and reports natively

•The ONLY change to the production environment is the removal of data. This removal must be vetted by functional people and tested thoroughly before implementing into production.

•The archive environment is read-only, no changes to archive or production data can be made.

•The archive data can sit in a different database or different schema within the same database.

•The process is completely reversible, if you want the archive data put back into JDE, the process can be run in reverse.

•Upgrade time will be quicker and easier. When it’s time to upgrade JDE, it’s simple to upgrade the archive data separately to the live data. This will ensure that the upgrade is completed on time!

•Using server and client specific scripts and criteria ensures the most efficient solution for each client’s needs.

•This can be achieved with NO additional software or hardware purchases

•Your archive environment allows you to have native E1 access to the superset of archive and production data.

What happens with a myArchive project:

myArchive gives you Native JDE access to all of your archive data and transactional data.  myArchive gives you all JDE screens and reports to query your data with.  You do not need to set up data warehouses and custom queries to interrogate your information.  You just use JDE.

We have customers live on this solution –  working in a production environment with half the data that was previously there.

Use our contact page to get more information on myArchive.

My first impressions of 9.1

Wow!

I think there are some great changes that make the application much easier to use.  Remember, I’m technical and my limited application experience might sway my thoughts in different directions.  First I like it, it’s very clean and crisp.

The tabs down the bottom are controlled with cookies, so it remembers what you were using last time you where in the application

I still jump left for fast path, but I like the new position.

Enhancement to my favourites? Please

There should be something under tools for “Add to favourites” when you are in an application.  I fast path to many applications (maybe all) – but I’d love to add them to my favourites…  How – cannot do it.  I still need to traverse the menus.  If one of the JDE design Gods reads this and thinks “That does not really breach our UI design guide lines”, let me know when it’s in.

This is a bit of a diatribe and I should not digress so readily with my wants.

What else is cool

General

it works with google chrome (well it did with 8.98.4.5 too, but this is good for me)  I’m addicted to chroming!

Inline lookups are excellent and a long time coming.  Who else forgets what they are looking up or why?

I believe that the application is faster!  We have it running locally and internet based and it really flies.  I’ve been super impressed with the interactive performance.

WSJ

WSJ I use a lot, a couple of small changes are great.  Firstly goes straight to the list of jobs – great (uses default UBE OCM).  Secondly it has “clickable” fields to view the PDF.  No slowing me down with click row, click row exit, view PDF…  Print with the click of a button too.  Drag and drop of columns is super simple.  Create your own fast and easy to use grid!

Advanced Query Functionality

The ability to save custom queries on any form is very powerful

So I only want to see the jobs for today – you can do it!

Only R0010P from today, no problems either.

So I now have these tabs on WSJ to quickly look at the jobs for today and yesterday!

These are saved as AQ type records in user overrides.  You can copy them to all your users.

How about this for a new default for Public?  My jobs that were launched today!

Perfect!

Auto Suggest

You now have the ability to create “auto suggest” lists from any field with config not code!

You need to be 9.1 application release – what a tease!

Pages and flows

Powerful graphical representation of flows and processes.

Oracle is making it easy to graphically represent a business flow with a highly configurable framework.

Turn this:

%Financial Planning Forecasting and Budgeting%
@A:Forcast Growth Patterns\runE1App('P1406','W1406A')
@B:Assign Patterns to Business Unit\runE1App('P1405','W1405A')
@C:Generate Forecast Basis\runE1UBEBlind('R1403','XJDE0001','0')
@D:Generate Forecast Results\runE1UBEBlind('R1404,'XJDE0001','0')
@E:Apply Forecast to Budget\runE1UBEBlind('R1407','XJDE0001','0')
@F:Requested Budget\runE1App('P14102','W14102A','ZJDE0001')
@G:Approved Budget\runE1App('P14102','W14102A','ZJDE0002')
@H:Final Budget\runE1App('P14102','W14102A','ZJDE0003')
@I:Budget vs. Actual Reporting\runE1App('P09210A','W09210AA','ZJDE0001')
A--->B-,
|
C-'->D--->E-,
,-------------------'
`->F--->G--->H--->I

Which can be seen from the users landing page!

We’ll be looking at these in more detail soon.

Twitter me this, twitter me that…

I’ve finally got onto the twitter bandwagon, and am very impressed by the medium of information dissemination.  I think if you want to keep up to date with the latest and greatest, twitter is the way forward.  I’m now going to be posting all my blogs to the Myriad Website (and to here), but also tweet the Myriad links. 

Follow @MyriadIT

Use the above link to follow MyriadIT and you’ll find out the blog posts as soon as I make them.

Tools release 9.1 is GA

Read more here:  http://www.oracle.com/us/corporate/press/1397069

I’ll be providing some value add analysis on the tools release and the improvements over the next couple of days.

E1 upgrades, where’s the ROI?

Introduction

I’ve said it…  Now who is going to put up their hand and answer the question?  “Where is the ROI in my ERP upgrade?” Why is someone going to take a 9.02 application upgrade at the moment?  Why is a client going to wait around for 9.1?  There are not many function reasons for doing so [of course 9.1 might blow our doors off].  There are a LOT of technical benefits though, all of which are exposed by the advances in tools releases and supported technology.  The expansion of “oracle technology foundation” has also played a large part in giving customers a reason to upgrade.

I’ve been involved with a number of pitches to clients, where the question (The elephant in the room) is asked – tell me why I should do this?  Tell me what I will gain from this upgrade?  Unless they have specific functional needs – which I will address in some detail, the answers are going to be based upon technology, supportability and compliance.

Very rarely these days the IT departments of large organisations have their own budget for an ERP upgrade, so getting the mandate for an application upgrade needs to come from the business.  If IT do get the budget, you can be sure that this will be based upon compliance reasons.  The business does not easily recognise the technical advancements or compliance issues when considering a new upgrade – the business wants cold hard advancements that are going to increase productivity or efficiency in their organisation.  How can this be done?  A journal is a journal, a sales order a sales order – the list goes on. It’s difficult to impossible to make these processes change dramatically, as it have the side effects of making upgrades harder and change management more difficult at upgrade time – which slashes the benefits of an upgrade.

Back to the old question – why upgrade.  We’ve just heard how leaps and bounds in existing modules are hard / expensive to implement as part of an upgrade.  New modules are a great idea, but generally for net new customers – rarely as a reason to upgrade.  If a fashion house is running JDE and want to upgrade because of apparel management module, then this is a reason – but they probably would not have bought JDE in the first place. 

The real reasons to upgrade (for JDE at the moment) are technical by nature, but what we must remember is that they generally have large functional benefits.  They can also have company side benefits if they are analysed carefully.

Lets see some of the enhancements in this simple table below and what are the technical and functional benefits:

Reasons to Upgrade

Technical

Better reporting tools – JDBC connectivity, interactive BI publisher

• You can have graphs on JDE forms
• You can have graphs on JDE reports
• You can host native BI portlets in JDE forms
• You can use type 4 JDBC driver for BI reporting
• BI publisher is available and a real alternative for third party products (option / create)

Optimize the execution of batch programs

• drill down into batch programs for performance and debugging
• real time logging and real data in terms of run time and what time
• Additional spread sheet based “BI for batch”

iPad support

• Native support for Safari on iPad – go mobile with JDE!

User Productivity increases

• Cut and paste between spread sheets and grids
• Right click context sensitive menus for form and row exits
• Parameterized URL

  The Simplified Parameterized URL is a feature that enables an external system to launch native JD Edwards EnterpriseOne application through the use a simple URL. This provides external applications the ability to have nearly seamless user interface integration with any available JD Edwards EnterpriseOne application

No downtime package deployment

• Certain packages do not need to lock the kernels on the server and can be deployed without fear of production BSFN timeouts.

Expanded platform options and support

• an ever increasing list of support
• Finally virtualisation is supported (OVS)
• Stay supported by all of your vendors

Simplified portal integrations

• JD Edwards EnterpriseOne WSRP Portlet Producer Running on Oracle WebLogic Server
• WSRP portlets can be deployed automatically when installing a JAS server
• The WSRP portlets can be exposed easily to Oracle Webcentre Spaces

RIA – Related information architecture

• simple way of linking the outside world with data on a form.  NO CODE simplicity
• An excellent enhancement for getting more out of JDE

Security Enhancements

• address book privacy
• media object security
• Oracle Enterprise Manager Data Masking Pack for JD Edwards EnterpriseOne
• Configuring webcentre security for E1

Grid Control management improvements

• You can chart metrics from E1 enterprise servers and web servers using oracle grid control framework
• Log file monitoring through Grid Control
• Application pack metrics

Better integration options

• Support of Oracle Enterprise Repository is a searchable repository that manages assets and relationships between assets Functional
• documented golden gate configurations for E1

Functional

Financial Management Enhancements

• Interest Invoice Print
• Journal Entry Voids
• IBAN in Payee Control
• Expense Management Usability

Human Capital Management Enhancements

• Many US centric enhancements

Project Management enhancements

• JD Edwards EnterpriseOne Business Accelerator for Engineering and Construction – General Contractors

Supply Chain Management

• Fulfillment Management (significant http://www.oracle.com/us/corporate/press/173466)
• Apparel Management (allows distribution and manufacturing companies to effectively manage multi-attribute products)

Integration to Primivera

• If you’re in the construction / project management industry, then you’ll know about Primavera.  Pre-built supported integrations to Primavera are ready to go in JDE.

Compliance / Support

Taking the risk out of your software is a massive reason for upgrade, and many upgrades are performed for this reason alone.  When this is the case, most clients to not consider the enhancements (technical or functional) they don’t look at the release notes – they begrudgingly accept the upgrade so that their browsers, server OS’s and databases are all supported.

 

What does all of this mean?

Are any one of those reasons compelling enough to sign up for your next E1 upgrade – for some people – YES!  For many customers, no.  Although if you were to consider the overall improvements that each of individual enhancements could do for you, the case becomes more compelling.

If you were to carefully read the technical enhancements that have been made between the tools releases and carefully read the functional enhancements – there are things that will make a difference to your business.  Some of them might be bigger than others.

I find that when I read the release notes, I learn more and more each time I get into the details.  There are so many great technical advancements in the tools, it’s difficult to know how much you can take advantage of.  You can get your finger on the pulse of fusion and where things are going when you look at how the JDE toolset is being integrated into things like Oracle Grid Control and Oracle Webcentre Spaces.  The abilities to display data graphically, not just in numbers is often forgotten.  Clients are going out buying expensive BI tools, when much of the basic functionality is exposed natively in JDE.

Oracle BI Publisher has been around JDE for a while now, bust must be considered to replace any 3rd party forms tools.  The volumes and complexity that BI Publisher produces makes it an obvious choice for large volume, high complexity reporting.

Simple things like cut and paste, right click sensitive forms and improved grid format processing will save users time.  These enhancements, when advertised properly, internally will make a productivity improvement and can change the way users interact with E1.

These are all good reasons to upgrade (tools at least).  

Summary:

It’s very critical to consider the architectural implications of upgrading your ERP and how these improvements can improve your business and your ability to respond to a changing environment.  Compliance might be the reason for your upgrade, but do not get blinded by this.  Look for other improvements that the business can make by doing the upgrade, look at least for the low hanging fruit.

Someone from the business needs to drive the need to research the new technology.  The use of BI, the use of real time open integration are great for the entire business.  It’s difficult to find the representative from the business with the budget to implement this type of technology. 

Integration alone should be reason enough to get on the latest tools release, use BSSV – get real-time.  Choose an integration methodology involving coordination and don’t be locked down by any of your vendors – because you can swap them out in a heartbeat.  Open integration will allow you to foster a “best of breed” mentality.  I’m not saying that an ERP is not the way to go, but sometimes it’s a competitive advantage to be able to chop and changes pieces of your architecture. Having a best of breed WMS that can talk realtime to SAAS vendors, to your ERP to anywhere.  Have your integration layer find you the best transportation deals / lowest supplier prices – this is the future.

References:

8.98 Update 4 Tools and Technology Documentation Overview
Provides a central location for the supporting documentation resources for the JD Edwards EnterpriseOne Tools and Technology Update 4 release.
Note: 1268837.1

8.98 Update 3 Tools and Technology Documentation Overview
Provides a central location for the supporting documentation resources for the JD Edwards EnterpriseOne Tools and Technology Update 3 release.
Note: 1082706.1

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!

"-XXcallProfiling -XXaggressive:opt" performance and JRockit and E1

The white (red) paper tells you to do it, better performance, less CPU utilisation on the web server.

What they don’t tell you is that if you view an RD job after you’ve set this setting, the JVM will go crazy and die.  So, do not change this setting if you are using BIP for EnterpriseOne. 

One a side note, when I changed this setting on my installation – it would not let me regress it.  I kept getting errors in the weblogic console when I removed those options from the JVM start options.  I then when and found that the file these are written to is called “config.xml” and can be found in ../domains/html_domain/config dir.   I’m guessing that you can edit this file (I’m not going to just yet) and restart your JVM’s and the settings should go away.

I’ll repost if this does not work.  I will be taking a backup of the file before I attempt this – that is for sure!

XE to ERP9 upgrade–Table conversions estimates

How long do you think F0911 conversion will take from XE to ERP9 – out of the box…  No smarts no nothing for 41000000 records.  This is on an AS/400 with V7R1 and plenty of memory and 1.1 CPU allocation.  (Note that this particular conversion only ever took up about 30% of CPU).

5h 33m or 333 minutes, nice number!

You would agree with me that that is a long time, and perhaps too long for some people.  How could you improve that time?  Most of the time is taken generating the 25 indexes that are required for F0911.  Unfortunately the indexes are run synchronously and without any parallelism (or default parallelism which is important for those of you that have enterprise edition of Oracle).

What I tend to do for these large (and simple) conversions is manually run the SQL (which is generally a join between the T and the base table) and do it all with as much parallelism as my system can handle.  This can improve performance of 333 minutes down to less than 1 hour.

F03B11 with 6.5 million takes about 64 minutes

OATS–Oracle Application Testing Suite–Performance testing JDE

OTS can be found here http://www.oracle.com/technetwork/oem/app-test/index.html

Full download here http://www.oracle.com/technetwork/oem/app-test/index-084446.html

This is the version 9.3 complete install.  Wow, only 1.4GB download!

The installer is pretty big, it’s going to install WebLogic 10.3.3 and also a XE database locally – so probably best to put it on a separate machine and it might have to be chunky (see specs below).  If you are using Oracle as your thick client local database, you might want to consider using a different machine also.

openscript is the first thing that you will run.  This gives you the ability to create scripts and test them.  I created a simple “load testing” HTML script:

This logged into JDE, did some address book queries and logged out again, nice and simple.

I was able to run this in the openscript console – all great. Note that this was using a weblogic 11G web server and tools release 8.98.4.5.

I tend to model core transactions for my clients, and try to create “a day in the life of X”.  I create different scenarios that stress different areas of the architecture.  Data manipulation, master BSFNs and UBEs for the back end.  Looking at lots of data and large grids, traversing menus for the web server and finally complex queries for the database.

I tend to stress the different tiers to get the low hanging fruit in the beginning.  Ensure that I have the correct JVM sizes and ensure that all logging is at a minimum.  It’s quite common that you’ll need to revise the concurrent connections in the jdbj.ini file for high usage.  I try and execute the “day in the life”

My favourite stress test / performance test for JDE is a “2 hours of power”.  Smash the machine with as many concurrent users (20% more than they anticipate) for 2 hours.  Ensure that the performance metrics are evan.  Check the JVM sizes and ensure they have not gone bad.  Ensure that ALL your transactions have hit the database (reconcile your load test).  Ensure that there is NO errors in the JAS logs and the enterprise server logs.  Too often a load test is “successful”, but has not completed properly at the back end.

Next thing you need to do is run some load testing, this is done within the web interface that runs within weblogic.

You need to start weblogic using startweblogic.cmd from within E:\oracleATS\oats dir – or where ever yours installed to:

The default URL is http://localhost:8088/olt/LoginSubmit.do

Oracle Application Automated Testing Suite default username is Administrator.  Why is this type of information very hard to find…

Login into the Test Manager or Admin console as Administrator with the password that you typed into the installation wizard.

Create a new scenario.  This is where you choose one or more of your “Tests”, as created within openscript. 

You choose concurrent VUs (virtual users), wait time options etc.  You can also choose where the script is going to run (system).  Then goto your “setup autopilot” tab:

By default the test will complete when you press STOP, you might want to change that… Run the test and watch the users in server manager.  You should see all of the screens changing when monitoring users.

This is the basics of OATS.  I know that it works with weblogic and 8.98.4.2 and 8.98.4.5.

I did some much more complicated load testing and OATS was a pleasure to use.  The remote agents were easy to configure too.  I used an underpowered test machine, I would recommend using a machine with a couple of processors, 8GB of memory and ensure that it’s GB LAN for the local load testing.

Data selection–List of values–speed entry!

Ever had a huge list of tables or programs or something that you wanted to automatically enter into a UBE’s data selection?  I know that I have many times.  Say I run the R9898711 and then want to run the R98403 to create all of the missing tables?  Well, I have a time saving suite for you.

step 1: Run the UBE to create CSV, so it’s easy to get a list of values that you want to enter into data selection.

step 2: use this formulae to create the appropriate “sendkeys” functions:  =+CONCATENATE("objshell.sendkeys """,A3, "~","""")

step 3.  populate column 1 of a spreadsheet with the list of items, and column 2 with the formula above

step 4. create a file called “Dataselection.vbs” on your computer and edit that file

step 5. paste in the following header

for B9

set objShell = wscript.createobject("WScript.Shell")

Do until success = True
  Success = objshell.AppActivate("List Of Values")
  'Success = objshell.AppActivate("something.txt - notepad")
  wscript.sleep 1000
Loop

wscript.sleep 100
wscript.echo "Start data pump"
wscript.sleep 100
Success = objshell.AppActivate("List Of Values")
objshell.sendkeys "+{tab}+{tab}"

For XE

set objShell = wscript.createobject("WScript.Shell")

Do until success = True
  Success = objshell.AppActivate("List Of Values")
  wscript.sleep 1000
Loop
wscript.sleep 100

step 6. paste column B from above underneath this header

objshell.sendkeys "F00021~"

objshell.sendkeys "F0010T~"

objshell.sendkeys "F0095~"

objshell.sendkeys "F01161DW~"

…

step 7.  save the file and get a command window

step 8. run “wscript.exe //e:vbscript Dataselection.vbs”

step 9.  Activeate the data selection, list of values screen that you want to add your values to

step 10.  The script will fire when the window is active and will populate the list!

 

Enjoy!

Data selection–List of values–speed entry!

Ever had a huge list of tables or programs or something that you wanted to automatically enter into a UBE’s data selection?  I know that I have many times.  Say I run the R9898711 and then want to run the R98403 to create all of the missing tables?  Well, I have a time saving suite for you.

step 1: Run the UBE to create CSV, so it’s easy to get a list of values that you want to enter into data selection.

step 2: use this formulae to create the appropriate “sendkeys” functions:  =+CONCATENATE("objshell.sendkeys """,A3, "~","""")

step 3.  populate column 1 of a spreadsheet with the list of items, and column 2 with the formula above

step 4. create a file called “Dataselection.vbs” on your computer and edit that file

step 5. paste in the following header (for B9)

set objShell = wscript.createobject("WScript.Shell")

Do until success = True
  Success = objshell.AppActivate("List Of Values")
  'Success = objshell.AppActivate("something.txt - notepad")
  wscript.sleep 1000
Loop

wscript.sleep 100
wscript.echo "Start data pump"
wscript.sleep 100
Success = objshell.AppActivate("List Of Values")
objshell.sendkeys "+{tab}+{tab}"

For XE

set objShell = wscript.createobject("WScript.Shell")

Do until success = True
  Success = objshell.AppActivate("List Of Values")
  wscript.sleep 1000
Loop
wscript.sleep 100

step 6. paste column B from above underneath this header

objshell.sendkeys "F00021~"

objshell.sendkeys "F0010T~"

objshell.sendkeys "F0095~"

objshell.sendkeys "F01161DW~"

…

step 7.  save the file and get a command window

step 8. run “wscript.exe //e:vbscript Dataselection.vbs”

step 9.  Activeate the data selection, list of values screen that you want to add your values to

step 10.  The script will fire when the window is active and will populate the list!

 

Enjoy!

JDEdwards on an iPad / iPhone

We’ve been able to get 8.98.4.5 running on our demo system and it’s pretty sweet on the iPad.  Instead of the “processing” wheel turning and turning, we actually have some applications loading and working.

If you’d like to experience the interface on your device, drop me an email and I’ll sort you out an account and a URL.

shannon dot moir at myriad-it dot com.  Sorry, but I don’t want bots picking up my email address.

It’s all demo data and you’ll get an account that will expire in a week and will be locked down – but you’ll be able to see JDE on your iDevice.

NTP on linux–must do!

I just had an interesting problem, I created a new user (actually copied it from JDE).  Copied all security roles etc – CNC101 stuff.

Logged in as the new user, no problems!  Security server working, web server validating roles and environments, CNC101!

Then each time I had to launch an application, I got the following:

29997/-263791728 WRK:Init Remote Env Token              Tue Oct  4 20:34:06.870621      jdb_ctl.c4310
        JDB1100029 - Failed to validate role: SYSADMIN for user: (null)

29997/-263791728 WRK:Init Remote Env Token              Tue Oct  4 20:34:06.878945      cnvtchra.c360
        PRT0000008 - Invalid Env handle

29997/-263791728 WRK:Init Remote Env Token              Tue Oct  4 20:34:06.879054      cnvtchra.c292
        PRT0000004 - Invalid Env handle

29997/-263791728 WRK:Init Remote Env Token              Tue Oct  4 20:34:06.879127      jdekinit.c1396
        JDB_ERR: FAILED TO VALIDATE ROLEALL FAILED TO SIGNON

I’ve been doing this for years, I can create a user…  After thinking for a while, and looking at the only difference is the start date for the role.  It was set to today (based on the web server time), but of course the enterprise server thinks it’s in new york!

So the kernels are doing a select on the F95921 using the current date and time and not getting any valid roles for my new user, as the current date on the server is 4th of Oct and when I created the user, the start date for the role I chose was defaulted to the 5th of October.

So, I quickly changed the timezone for the enterprise server, restarted JDE and logged in again – all is good.

Lesson here, even if it’s a demo server (which mine is – a templated demo server at that!).  Make sure that you set up all of the NTP properly and the timezones properly so that the JDE security tokens and role expirations are going to work properly.

Simple script for getting JDE DD descriptions for column names in oracle

select column_name, frdscr, ltrim(rtrim(substr(column_name, 3, length(column_name)-2))), ltrim(rtrim(frdtai))
from all_tab_columns, dd900.f9202
where FRSYR = '  '
and ltrim(rtrim(substr(column_name, 3, length(column_name)-2))) = ltrim(rtrim(frdtai))
and table_name = 'F4201' and owner = 'TESTDTA'

Load testing RTE–generating the load

I don’t know if I found a bug, but the SQLServer trigger for F90710 does not fire for bulk inserts.

so if you do “insert into select * from”, not all of the rows get a new unique ID and a timestamp.

So, you can use this little gem to get some records into the F90710 for load testing:

Firstly wait till there is a little bit of data in the F90710 and then execute this:

select * into testdta.F90710SRM from TESTDTA.F90710 where etevntname = 'RTSOOUT' ;

You’ll now have a copy of the F90710 without triggers.

Simply choose a record that you want to replicate in the SRM table and get it’s ETEVNTID “'E90LOG8_2181109774_6015_4284_083020111637491'”  Plug that into the yellow bit below

-- Declare an iterator
DECLARE @I INT
-- Initialize the iterator
SET @I = 1

WHILE (@I <= 500)
BEGIN

    insert into TESTDTA.F90710
    (ETEVNTID,ETEVNTSEQ,ETEVNTTIME,ETEVNTNAME,ETEVNTTYPE,ETEVNTST,ETENV,ETEVNTUSER,ETUGRP,ETOBNM,ETVER,
    ETEVNTSNS,ETEVNTSCOPE,ETEVNTHOST,ETEVNTSRT,ETEVNTBSFN,ETFCTNM,ETEVNTPRID,ETEDATA,ETPID,ETUSER,
    ETMKEY,ETUPMJ,ETUPMT)
    select
    rtrim(ETEVNTID) + convert(varchar(4),@I),ETEVNTSEQ,ETEVNTTIME,ETEVNTNAME,ETEVNTTYPE,ETEVNTST,ETENV,ETEVNTUSER,
    ETUGRP,ETOBNM,ETVER,ETEVNTSNS,ETEVNTSCOPE,ETEVNTHOST,ETEVNTSRT,ETEVNTBSFN,ETFCTNM,ETEVNTPRID,
    ETEDATA,ETPID,ETUSER,ETMKEY,ETUPMJ,ETUPMT
    from TESTDTA.F90710srm where rtrim(ETEVNTID) = 'E90LOG8_2181109774_6015_4284_083020111637491';
       
        PRINT 'Row No = ' + CONVERT(VARCHAR(4), @I)
        SET @I = @I  + 1
END

The above will place 500 rows into the table and will fire the triggers to get a proper sequence etc.

I want my RTE events, but only from certain programs

Is this something that you want from JDE.  Sure you love RTE events, you love their speed and reliability (um, do you detect a certain amount of sarcasm)? No RTE is pretty good for getting some real time messages out of the JDE.

So clients want their RTE from P4210, but do not want them when R42950 runs.  They could modify the SOE master business function and add some exceptions there, but that is fraught with danger.

So, I’ve devised a simple and cunning plan to only process the RTE records that you want.

Firstly, create a UDC 55|RT with a code length of 10.

Add the programs that you want RTE’s to fire from in this list.  A simple way to get a list is to turn of deletion of EVENTS and look at the data in the F90710.  Group by event type and OBNM, and you’ll soon see the ones that you want and the ones that you don’t.

Now, I know you could filter these out further down the track (like in SOA), but you might find (like me) that the performance of the RTE is less than satisfactory.

So, I’m going to cull the records in the F90710, so I get as many decent transactions into the JSM queues as I can.  Behold, a modification to the trigger that JDE installs on the F90710.

Any the beauty of it is, simplicity! 

It just checks the OBNM of the incoming F90710 record, if it’s in the list of acceptable programs – we let it through.  If not, it stays at 2 (just in case we need to process it later).  No need to change the trigger for more programs, just add to the UDC.

Note that this trigger is designed for F90710 that is in TESTDTA.  This is also SQLServer syntax (I did not have to tell you that if you are still reading this!).  If you are going to put this into prod, you’ll need to prefix the owner of the UDC table with database name.  Also, there are NO guarantees or Warranties with this, you must do lots of testing before putting this into PROD!

 

USE [JDE_DEVELOPMENT]
GO
/****** Object:  Trigger [TESTDTA].[F90710_EVT_RIB]    Script Date: 08/31/2011 12:55:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER trigger [TESTDTA].[F90710_EVT_RIB] on [TESTDTA].[F90710] for insert as
begin
  set nocount on

  declare @nRowsAffected int, @seqnum decimal(38), @eventid char(255), @OBNM nchar(10), @ValidProgram int

  select @nRowsAffected = count(*) from inserted
  select @OBNM = ETOBNM from inserted
  select @validProgram = count(*) from testctl.f0005 WHERE DRSY = '55'    AND DRRT = 'RT' and DRKY = @OBNM

  if (@nRowsAffected > 0 and @validProgram > 0)
  begin
    declare inserted_cursor cursor local for select ETEVNTID from inserted
    open inserted_cursor
    fetch next from inserted_cursor into @eventid   
   
    if (@@fetch_status = 0)
    begin
      close inserted_cursor
      deallocate inserted_cursor

      declare seqnum_cursor cursor local dynamic scroll_locks for select USUKID from TESTDTA.F90730 where USOBNM = 'EVENT_SEQ' for update of USUKID
      open seqnum_cursor
      fetch next from seqnum_cursor into @seqnum
      if (@@fetch_status = 0)
      begin
        update TESTDTA.F90730 set USUKID = @seqnum + 1 where current of seqnum_cursor
        update TESTDTA.F90710 set ETEVNTTIME = getdate(), ETEVNTST = 3, ETEVNTSEQ = @seqnum where ETEVNTID = @eventid
      end
      close seqnum_cursor
      deallocate seqnum_cursor
    end
  end
end

SQL Server quick compare of central objects to Database for 1 table

SELECT name
FROM jde_development.sys.columns
WHERE object_id = OBJECT_ID('jde_development.testdta.F95625')
and not exists (select 1 from JDE_DV900.dv900.f98711
where TDOBNM = 'F95625' and ltrim(rtrim(TDSQLC)) = name);

select TDSQLC from JDE_DV900.dv900.f98711
where TDOBNM = 'F95625'
and not exists
(SELECT 1
FROM jde_development.sys.columns
WHERE object_id = OBJECT_ID('jde_development.testdta.F95625')
and ltrim(rtrim(TDSQLC)) = name)

Keep your transactions in the transaction server

As you know, RTE’s are written to F90710 with a status 2 from JDE.

The database trigger updates the record to a status 3 and also updates the event sequence (this is important)

The RTE then updates to a 4 – saying that it’s trying to put the message into a Queue.

If successful the transaction server will delete the event.

If you want to keep your events, ensure that the following setting is in the TXN servers JAS.INI

[EVENTS]

deleteCompletedEvents=FALSE

The events will then be kept in the F90710 at a status 5.

update TESTDTA.F90730 set USUKID = @seqnum + 1 where current of seqnum_cursor
        update TESTDTA.F90710 set ETEVNTTIME = getdate(), ETEVNTST = 3, ETEVNTSEQ = @seqnum where ETEVNTID = @eventid

Remember that this is an insert based trigger, so if you want to process events again without breaking the sequence number in the transaction server.  You need to copy the records out to a temp F90710, update the status to a 2 and insert them again.

Row security causes poor performance, a common misnomer

Introduction

The use of row security is not necessarily a performance bottleneck, or something that effects performance negatively.  Sometimes (and quite often) performance can improve with row security enabled.  You are actually processing less rows in many open queries and you are helping the optimiser with more columns to put into the QEP.

Firstly, all row security is cached on login, there is one select on the F00950 for all rows for the environment in question. The web engine caches all records so that there is no additional impact if there is additional records. I appreciate that there will be more records, but this load does not generally affect performance.

Secondly, row security is generally applied to “keyed” columns. Generally row security is applied to Company (CO) or business unit (MCU) fields. These fields are often in indexes and therefore, do not cause table scans or reduce performance. If it is seen that this does occur for some queries, additional indexes should be created.

Inclusive or exclusive?

When defining Row Security in the Security Workbench application (P00950), you have the option to define the row security based on either Inclusive or Exclusive Row Security settings.

Inclusive Row Security allows for defining valid ranges of values in the Row Security From and Thru Values and the user/role on those security records would have access to only records lying within that range. Any records outside of the defined range would be secured from the user/role.

This is the opposite of using exclusive row security where the ranges that are defined in the From and Thru Values are those that the user/role should not have access to. When defining Exclusive Row Security, the user will only have access to records that are not within the defined range.

I generally choose inclusive security.

Row security exceptions:

You cannot apply row security to bootstrap tables (in general).

The following is an example list of bootstrap tables : F0092, F0093, F0094, F98OWSEC, F00165, F00921, F00922, F00924, F00925, F00926, F00941, F00942, F00945, F00948, F00960, F9200, F9202, F9203, F9207, F9210, F9211, F9312, F9650, F9860, F9861, F9862, F9863, F9865, F9885, F9886, F9887, F9888, F9889, F95921, F98101, F98611, F98613, F98710, F98712, F98713, F98720, F98740, F98741, F98743, F98745, F98750, F98751, F98752, F98753, F98760, F98761, F98762, F98891, F98950, F983051, F986101, F986110, F986111.

Conclusion:

The introduction of row security can have positive effects on performance. Quite often it will reduce data sets being processed for many batch and interactive application. It’s a way of enforcing a quasi “QBE entry” against company or MCU.

In general these columns are keyed well, if you find that there are hot-spots, some custom indexes will address this quickly.

view data from failed RTEs with SQL Server

SELECT convert(nvarchar(4000),convert(varbinary(4000),ftedata)) from SY900.F90712 order by FTEVNTTIME desc;

What is my JDNI URL for RTE in SM going to be (for OAS)

So you are faced with a screen like the above, how are you going to find out the correct jdni port to use?  It seems that there are a number of support documents that state things like:

x. Provider URL: opmn:ormi://ServerABC:6003:ContainerXYZ (where 'ServerABC' is the name of your Transaction Server and 'ContainerABC' is OAS the container containing the application)

and to use 6003, it’s the default…  That is all well and good, but who is actually default?

A good article is found here:

http://download.oracle.com/docs/cd/B12314_01/web.904/b10326/ormi.htm#1041830

opmnctl status –l

Processes in Instance: SYCBSSV.xxx.com.au
---------------------------------+--------------------+---------+----------+------------+----------+-----------+------
ias-component                    | process-type       |     pid | status   |        uid |  memused |    uptime | ports
---------------------------------+--------------------+---------+----------+------------+----------+-----------+------
OC4JGroup:default_group          | OC4J:DVBSSV2       |    5812 | Alive    |        509 |   222340 |   0:06:03 | jms:12603,ajp:12502,rmis:12703,rmi:12403
OC4JGroup:default_group          | OC4J:DVTRNSRV      |    6028 | Alive    |        503 |   480508 | 646:53:53 | jms:12604,ajp:12503,rmis:12704,rmi:12404
OC4JGroup:default_group          | OC4J:PDBSSV        |    7880 | Alive    |        502 |   621536 | 647:23:11 | jms:12602,ajp:12501,rmis:12702,rmi:12402
OC4JGroup:default_group          | OC4J:home          |    4164 | Alive    |        495 |   328380 | 1388:58:~ | jms:12601,ajp:8888,rmis:12701,rmi:12401
ASG                              | ASG                |     N/A | Down     |        N/A |      N/A |       N/A | N/A
HTTP_Server                      | HTTP_Server        |    4688 | Alive    |        494 |    86324 | 1388:58:~ | http6:6080,http5:8883,http4:8884,http3:8881,https1:443,http2:7200,http1:92

It’s the RMI port that you should use for your JNDI queries, so grab the RMI port from the output above and use that:

So, if you used 12404, you’d be good!

e1agent as a service on linux with auto start

Even with OEL templates, the auto start an stop of aspects of JDE is not great, so I’m on a mission to change this.  I’m a little stunned that the templates do not have this built in, to be honest.   I’m going to write service control scripts (to go into /etc/init.d) to allow you to easily start and stop processes with “service” controls.  You might even be able to use the GUI interface that gnome-session provides (see diagram later in this post).

With the installation of the above, you’ll be able to see if the agent is running and easily stop and start the agent.

This requires a couple of things:

Firstly,  a config file called e1Agent in /etc/sysconfig, with contents like the below:  remember to change your e1agent home and e1_agent_user to the site specific values

#Config values for e1Agent
e1_agent_home=/u01/jdedwards/jde_home
e1_agent_user=oracle

Once this is done, you need to create the /etc/init.d/e1Agent file with the following contents:

#!/bin/sh
#
# /etc/rc.d/init.d/e1Agent
#
# Starts JDE Management Agent
#
# chkconfig: 345 99 00
# description: e1Agent service control script
#

## get success, failure function
. /etc/init.d/functions

#Get the E1 specifics
if [ -f /etc/sysconfig/e1Agent ]; then
    . /etc/sysconfig/e1Agent
fi

# userid to run server as
: ${e1_agent_user:=oracle}
# CHANGETHIS: Installation home for Agent
: ${e1_agent_home:="/u01/jdedwards/jde_home"}

prog="               0racle E1 Agent"
: ${pidfile:="/var/run/e1Agent.pid"}
: ${e1_agent_start_log:=${e1_agent_home}/logs/e1agent_0.log}

## Summary
## Start:
##    Background a runAgent.sh by the installation user,
## Stop:
##    Kill the e1agent ("java") processes

RETVAL=0
case "$1" in
  start)
    if [ ! -x ${e1_agent_home}/bin/runAgent ]; then
        echo ${e1_agent_home}/bin/runAgent not executable. >&2
                failure
        exit 1
    fi
        if [ `ps -ef |grep java |grep -v grep | grep scfagent.jar | wc -l` -gt 0 ]; then
                echo "Agent is already running"
                failure
                exit 1
        fi

    ## runAgent
    ## - should be started as installation user (ex.bea,weblogic,oracle)
    ## - runs in foreground, so background here. Record pid
        echo -n $"Starting $prog: "
    sudo -u ${e1_agent_user} ${e1_agent_home}/bin/runAgent > ${e1_agent_start_log} 2>&1 < /dev/null &
    e1_agent_pid=$!    ;# assume sudo exec(2)s, not fork

    echo $e1_agent_pid > ${pidfile}

    touch /var/lock/subsys/e1Agent
    success $"         e1 Agent startup"
    ;;
  stop)
     ##  Try stopAgent first
    if [ ! -x ${e1_agent_home}/bin/stopAgent ]; then
        echo ${e1_agent_home}/bin/stopAgent not executable. >&2
        exit 1
    fi

    read e1_agent_pid < ${pidfile}
    test -z "$e1_agent_pid" && exit 1
   
    #sudo -u ${e1_agent_user} ${e1_agent_home}/bin/stopAgent > ${e1_agent_start_log} 2>&1 < /dev/null
    sudo -u ${e1_agent_user} ${e1_agent_home}/bin/stopAgent > ${e1_agent_start_log} 2>&1

    if [ `ps -ef |grep -v grep |grep $e1_agent_pid |wc -l` -eq 0 ] ; then
          rm -f /var/lock/subsys/e1Agent
          rm -f ${pidfile}
      success $"          e1 agent shutdown"
        else
          echo 'stop agent unsuccessful, try forcestop'
    fi         
    ;;
  forcestop)
    ## easy way: kill away all weblogic-ish java
    echo -n $"Stopping $prog: "
    kill -9 `ps -ef |grep scfagent.jar |grep -v grep | awk '{print($2)}'`
        rm -f /var/lock/subsys/e1Agent
        rm -f ${pidfile}
    ;;
  statusverbose)
    ## give some nerdy information
        echo "Config file: /etc/sysconfig/e1Agent"
        if [ `ps -ef |grep scfagent.jar |grep -v grep | wc -l` -eq 1 ]; then
          success $"          gent is running"
        else
          failure
        fi
        read e1_agent_pid < ${pidfile}
        echo ${pidfile} indicates pid is ${e1_agent_pid}
        if [ -f /var/lock/subsys/e1Agent ]; then
          ls -l /var/lock/subsys/e1Agent
          echo "Lock files exists"
        else
          echo "no lockfile"
        fi
        echo "refer to " ${e1_agent_home}/logs/e1agent_0.log " for more info"
    ;;
  status)
        if [ `ps -ef |grep scfagent.jar |grep -v grep | wc -l` -eq 1 ]; then
            read e1_agent_pid < ${pidfile}
            echo e1Agent \(pid ${e1_agent_pid}\) is running
        else
            echo "e1Agent is stopped"
        fi
    ;;
  restart)
    ## check that its running and then restart
    read e1_agent_pid < ${pidfile}
    test -z "$e1_agent_pid" && exit 1
    if [ `ps -ef |grep -v grep |grep scfagent.jar | grep $e1_agent_pid | wc -l` -gt 0 ]; then
            #assumption is that proc is alive, restart!
            ${e1_agent_home}/bin/restartAgent > ${e1_agent_start_log} 2>&1 < /dev/null &
            e1_agent_pid=$!
        echo $e1_agent_pid > ${pidfile}
        touch /var/lock/subsys/e1Agent
            success $"           Oracle e1 Agent restart"
        else
            echo "Agent not running, cannot restart - try starting"
            exit 1
        fi
    ;;
  *)
    echo $"Usage: $0 {start|stop|forcestop|restart|status|statusverbose}"
    exit 2
esac

exit $RETVAL

finally, as root, execute the following to enable autostart:

chkconfig e1Agent on

So, you can now execute service e1Agent start|stop|restart|status|statusverbose and it’ll start or stop or do what you need.

You’ll also see the service listed if you look at the e1Agent service through something like gnome-session.

SQL to quickly create a full package on server (>8.12) only spec files, not DLLs

This is all of the SQL you’ll need to create PD900FA from a (very) fresh copy of central objects.  I’ve needed to do this when installing a new machine and the tables did not exist.  I don’t recommend this for running production sites on, use proper package build and deploy. This might however, get you out of a bind.

--  Generate SQL
--  Version:                       V7R1M0 100423
--  Generated on:                  05/08/11 13:22:15
--  Relational Database:           SVR703
--  Standards Option:              DB2 for i
 
CREATE TABLE COPD900.F98306PD900FA (
    PTOBNM GRAPHIC(10) CCSID 13488 DEFAULT NULL ,
    PTPOTP GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    PTITNUM DECIMAL(5, 0) DEFAULT NULL ,
    PTSQNUM DECIMAL(5, 0) DEFAULT NULL ,
    PTLNGP GRAPHIC(2) CCSID 13488 DEFAULT NULL ,
    PTCRTU GRAPHIC(10) CCSID 13488 DEFAULT NULL ,
    PTUPMJ NUMERIC(6, 0) DEFAULT NULL ,
    PTTDAY DECIMAL(6, 0) DEFAULT NULL ,
    PTPOTX DBCLOB(30000) CCSID 13488 DEFAULT NULL ,
    PTPGTX VARGRAPHIC(255) CCSID 13488 DEFAULT NULL ,
    PTGKEY GRAPHIC(10) CCSID 13488 DEFAULT NULL ,
    PTHELPID1 DECIMAL(31, 0) DEFAULT NULL ,
    PTHFNAME GRAPHIC(10) CCSID 13488 DEFAULT NULL ,
    CONSTRAINT COPD900.F98306PD900FA_PK PRIMARY KEY( PTOBNM , PTPOTP , PTITNUM , PTSQNUM , PTLNGP ) )  
    ;
 
CREATE TABLE COPD900.F98710PD900FA (
    THTBID DECIMAL(31, 0) DEFAULT NULL ,
    THOBNM GRAPHIC(10) CCSID 13488 DEFAULT NULL ,
    THNCOL NUMERIC(4, 0) DEFAULT NULL ,
    THNPIN NUMERIC(4, 0) DEFAULT NULL ,
    THNFIN NUMERIC(4, 0) DEFAULT NULL ,
    THJDEVERS GRAPHIC(10) CCSID 13488 DEFAULT NULL ,
    THMRGMOD GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    THMRGOPT GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    THFFU1 GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    THFFU2 GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    CONSTRAINT COPD900.F98710PD900FA_PK PRIMARY KEY( THOBNM ) )  
    ;
 
CREATE TABLE COPD900.F98711PD900FA (
    TDTBID DECIMAL(31, 0) DEFAULT NULL ,
    TDOBNM GRAPHIC(10) CCSID 13488 DEFAULT NULL ,
    TDDDID DECIMAL(31, 0) DEFAULT NULL ,
    TDOBND GRAPHIC(11) CCSID 13488 DEFAULT NULL ,
    TDPSEQ DECIMAL(5, 0) DEFAULT NULL ,
    TDSQLC GRAPHIC(30) CCSID 13488 DEFAULT NULL ,
    TDCID DECIMAL(11, 0) DEFAULT NULL ,
    TDJDEVERS GRAPHIC(10) CCSID 13488 DEFAULT NULL ,
    TDMRGMOD GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    TDMRGOPT GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    TDFFU1 GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    TDFFU2 GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    CONSTRAINT COPD900.F98711PD900FA_PK PRIMARY KEY( TDOBNM , TDOBND ) )  
    ;
 
CREATE TABLE COPD900.F98712PD900FA (
    TPTBID DECIMAL(31, 0) DEFAULT NULL ,
    TPOBNM GRAPHIC(10) CCSID 13488 DEFAULT NULL ,
    TPINID DECIMAL(31, 0) DEFAULT NULL ,
    TPDESC GRAPHIC(30) CCSID 13488 DEFAULT NULL ,
    TPPRMF GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    TPUNIQ GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    TPNDET NUMERIC(4, 0) DEFAULT NULL ,
    TPJDEVERS GRAPHIC(10) CCSID 13488 DEFAULT NULL ,
    TPMRGMOD GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    TPMRGOPT GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    TPFFU1 GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    TPFFU2 GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    CONSTRAINT COPD900.F98712PD900FA_PK PRIMARY KEY( TPOBNM , TPINID ) )  
    ;
 
CREATE TABLE COPD900.F98713PD900FA (
    TLTBID DECIMAL(31, 0) DEFAULT NULL ,
    TLOBNM GRAPHIC(10) CCSID 13488 DEFAULT NULL ,
    TLINID DECIMAL(31, 0) DEFAULT NULL ,
    TLDESC GRAPHIC(30) CCSID 13488 DEFAULT NULL ,
    TLDDID DECIMAL(31, 0) DEFAULT NULL ,
    TLOBND GRAPHIC(11) CCSID 13488 DEFAULT NULL ,
    TLCMPI NUMERIC(4, 0) DEFAULT NULL ,
    TLSRTO GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    TLCFLD NUMERIC(4, 0) DEFAULT NULL ,
    TLJDEVERS GRAPHIC(10) CCSID 13488 DEFAULT NULL ,
    TLMRGMOD GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    TLMRGOPT GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    TLFFU1 GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    TLFFU2 GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    CONSTRAINT COPD900.F98713PD900FA_PK PRIMARY KEY( TLOBNM , TLOBND , TLINID ) )  
    ;
 
CREATE TABLE COPD900.F98720PD900FA (
    BVOBNM GRAPHIC(10) CCSID 13488 DEFAULT NULL ,
    BVBVID DECIMAL(31, 0) DEFAULT NULL ,
    BVBVBLOB BLOB(15728632) DEFAULT NULL ,
    BVJDEVERS GRAPHIC(10) CCSID 13488 DEFAULT NULL ,
    BVMRGMOD GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    BVMRGOPT GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    BVFFU1 GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    BVFFU2 GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    CONSTRAINT COPD900.F98720PD900FA_PK PRIMARY KEY( BVOBNM ) )  
    ;
 
CREATE TABLE COPD900.F98740PD900FA (
    ELOBNM GRAPHIC(10) CCSID 13488 DEFAULT NULL ,
    ELEVSPEC DECIMAL(31, 0) DEFAULT NULL ,
    ELPRDTYP DECIMAL(31, 0) DEFAULT NULL ,
    ELAPPLID DECIMAL(31, 0) DEFAULT NULL ,
    ELFORMID DECIMAL(31, 0) DEFAULT NULL ,
    ELCTRLID DECIMAL(31, 0) DEFAULT NULL ,
    ELWEVENT DECIMAL(15, 0) DEFAULT NULL ,
    ELERID3 DECIMAL(31, 0) DEFAULT NULL ,
    ELERBLOB BLOB(15728632) DEFAULT NULL ,
    ELJDEVERS GRAPHIC(10) CCSID 13488 DEFAULT NULL ,
    ELMRGMOD GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    ELMRGOPT GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    ELFFU1 GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    ELFFU2 GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    ELVERS GRAPHIC(10) CCSID 13488 DEFAULT NULL ,
    ELFMNM GRAPHIC(10) CCSID 13488 DEFAULT NULL ,
    ELEVSK GRAPHIC(36) CCSID 13488 DEFAULT NULL ,
    CONSTRAINT COPD900.F98740PD900FA_PK PRIMARY KEY( ELPRDTYP , ELOBNM , ELVERS , ELFMNM , ELCTRLID , ELWEVENT , ELERID3 ) )  
    ;
 
CREATE TABLE COPD900.F98741PD900FA (
    ESEVSPEC DECIMAL(31, 0) DEFAULT NULL ,
    ESEVSEQ DECIMAL(31, 0) DEFAULT NULL ,
    ESERBLOB BLOB(15728632) DEFAULT NULL ,
    ESJDEVERS GRAPHIC(10) CCSID 13488 DEFAULT NULL ,
    ESMRGMOD GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    ESMRGOPT GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    ESFFU1 GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    ESFFU2 GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    ESEVSK GRAPHIC(36) CCSID 13488 DEFAULT NULL ,
    CONSTRAINT COPD900.F98741PD900FA_PK PRIMARY KEY( ESEVSK , ESEVSEQ ) )  
    ;
 
CREATE TABLE COPD900.F98743PD900FA (
    DTOBNM GRAPHIC(10) CCSID 13488 DEFAULT NULL ,
    DTTMPLTYP NUMERIC(2, 0) DEFAULT NULL ,
    DTTMPLID DECIMAL(31, 0) DEFAULT NULL ,
    DTTMPLNM GRAPHIC(15) CCSID 13488 DEFAULT NULL ,
    DTERBLOB BLOB(15728632) DEFAULT NULL ,
    DTJDEVERS GRAPHIC(10) CCSID 13488 DEFAULT NULL ,
    DTMRGMOD GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    DTMRGOPT GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    DTFFU1 GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    DTFFU2 GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    CONSTRAINT COPD900.F98743PD900FA_PK PRIMARY KEY( DTOBNM ) )  
    ;
 
CREATE TABLE COPD900.F98745PD900FA (
    SFOBNM GRAPHIC(10) CCSID 13488 DEFAULT NULL ,
    SFNMNAME GRAPHIC(10) CCSID 13488 DEFAULT NULL ,
    SFDESC GRAPHIC(30) CCSID 13488 DEFAULT NULL ,
    SFERBLOB BLOB(15728632) DEFAULT NULL ,
    SFJDEVERS GRAPHIC(10) CCSID 13488 DEFAULT NULL ,
    SFMRGMOD GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    SFMRGOPT GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    SFFFU1 GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    SFFFU2 GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    CONSTRAINT COPD900.F98745PD900FA_PK PRIMARY KEY( SFOBNM , SFNMNAME ) )  
    ;
 
CREATE TABLE COPD900.F98750PD900FA (
    FTOBNM GRAPHIC(10) CCSID 13488 DEFAULT NULL ,
    FTAPPLID DECIMAL(31, 0) DEFAULT NULL ,
    FTTEXTID DECIMAL(31, 0) DEFAULT NULL ,
    FTLNGP GRAPHIC(2) CCSID 13488 DEFAULT NULL ,
    FTSY GRAPHIC(4) CCSID 13488 DEFAULT NULL ,
    FTFDABLOB BLOB(15728632) DEFAULT NULL ,
    FTJDEVERS GRAPHIC(10) CCSID 13488 DEFAULT NULL ,
    FTMRGMOD GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    FTMRGOPT GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    FTFFU1 GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    FTFFU2 GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    CONSTRAINT COPD900.F98750PD900FA_PK PRIMARY KEY( FTOBNM , FTTEXTID , FTLNGP , FTSY ) )  
    ;
 
CREATE TABLE COPD900.F98751PD900FA (
    FSOBNM GRAPHIC(10) CCSID 13488 DEFAULT NULL ,
    FSCTRLID DECIMAL(31, 0) DEFAULT NULL ,
    FSAPPLID DECIMAL(31, 0) DEFAULT NULL ,
    FSRCRDTP NUMERIC(2, 0) DEFAULT NULL ,
    FSGNCID1 DECIMAL(31, 0) DEFAULT NULL ,
    FSGNCID2 DECIMAL(31, 0) DEFAULT NULL ,
    FSWEVENT DECIMAL(15, 0) DEFAULT NULL ,
    FSGNCID3 DECIMAL(31, 0) DEFAULT NULL ,
    FSFDABLOB BLOB(15728632) DEFAULT NULL ,
    FSJDEVERS GRAPHIC(10) CCSID 13488 DEFAULT NULL ,
    FSMRGMOD GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    FSMRGOPT GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    FSFFU1 GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    FSFFU2 GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    FSFMNM GRAPHIC(10) CCSID 13488 DEFAULT NULL ,
    CONSTRAINT COPD900.F98751PD900FA_PK PRIMARY KEY( FSOBNM , FSRCRDTP , FSFMNM , FSGNCID2 , FSWEVENT , FSGNCID3 ) )  
    ;
 
CREATE TABLE COPD900.F98752PD900FA (
    AHOBNM GRAPHIC(10) CCSID 13488 DEFAULT NULL ,
    AHAPPLID DECIMAL(31, 0) DEFAULT NULL ,
    AHFDABLOB BLOB(15728632) DEFAULT NULL ,
    AHJDEVERS GRAPHIC(10) CCSID 13488 DEFAULT NULL ,
    AHMRGMOD GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    AHMRGOPT GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    AHFFU1 GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    AHFFU2 GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    CONSTRAINT COPD900.F98752PD900FA_PK PRIMARY KEY( AHOBNM ) )  
    ;
 
CREATE TABLE COPD900.F98753PD900FA (
    ADOBNM GRAPHIC(10) CCSID 13488 DEFAULT NULL ,
    ADAPPLID DECIMAL(31, 0) DEFAULT NULL ,
    ADFMNM GRAPHIC(10) CCSID 13488 DEFAULT NULL ,
    ADFRMID DECIMAL(31, 0) DEFAULT NULL ,
    ADFDABLOB BLOB(15728632) DEFAULT NULL ,
    ADJDEVERS GRAPHIC(10) CCSID 13488 DEFAULT NULL ,
    ADMRGMOD GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    ADMRGOPT GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    ADFFU1 GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    ADFFU2 GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    CONSTRAINT COPD900.F98753PD900FA_PK PRIMARY KEY( ADOBNM , ADFMNM ) )  
    ;
 
CREATE TABLE COPD900.F98760PD900FA (
    RTOBNM GRAPHIC(10) CCSID 13488 DEFAULT NULL ,
    RTREPORTID DECIMAL(31, 0) DEFAULT NULL ,
    RTTEXTID DECIMAL(31, 0) DEFAULT NULL ,
    RTLNGP GRAPHIC(2) CCSID 13488 DEFAULT NULL ,
    RTSY GRAPHIC(4) CCSID 13488 DEFAULT NULL ,
    RTRDABLOB BLOB(15728632) DEFAULT NULL ,
    RTJDEVERS GRAPHIC(10) CCSID 13488 DEFAULT NULL ,
    RTMRGMOD GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    RTMRGOPT GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    RTFFU1 GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    RTFFU2 GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    RTVERS GRAPHIC(10) CCSID 13488 DEFAULT NULL ,
    CONSTRAINT COPD900.F98760PD900FA_PK PRIMARY KEY( RTOBNM , RTVERS , RTTEXTID , RTLNGP , RTSY ) )  
    ;
 
CREATE TABLE COPD900.F98761PD900FA (
    RSOBNM GRAPHIC(10) CCSID 13488 DEFAULT NULL ,
    RSCTRLID DECIMAL(31, 0) DEFAULT NULL ,
    RSREPORTID DECIMAL(31, 0) DEFAULT NULL ,
    RSRCRDTP NUMERIC(2, 0) DEFAULT NULL ,
    RSGNCID1 DECIMAL(31, 0) DEFAULT NULL ,
    RSGNCID2 DECIMAL(31, 0) DEFAULT NULL ,
    RSWEVENT DECIMAL(15, 0) DEFAULT NULL ,
    RSGNCID3 DECIMAL(31, 0) DEFAULT NULL ,
    RSRDABLOB BLOB(15728632) DEFAULT NULL ,
    RSJDEVERS GRAPHIC(10) CCSID 13488 DEFAULT NULL ,
    RSMRGMOD GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    RSMRGOPT GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    RSFFU1 GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    RSFFU2 GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    RSFMNM GRAPHIC(10) CCSID 13488 DEFAULT NULL ,
    RSVERS GRAPHIC(10) CCSID 13488 DEFAULT NULL ,
    CONSTRAINT COPD900.F98761PD900FA_PK PRIMARY KEY( RSOBNM , RSVERS , RSRCRDTP , RSGNCID1 , RSGNCID2 , RSWEVENT , RSGNCID3 ) )  
    ;
 
CREATE TABLE COPD900.F98762PD900FA (
    BCOBNM GRAPHIC(10) CCSID 13488 DEFAULT NULL ,
    BCBHVRID DECIMAL(31, 0) DEFAULT NULL ,
    BCSRCFNM GRAPHIC(80) CCSID 13488 DEFAULT NULL ,
    BCRDABLOB BLOB(15728632) DEFAULT NULL ,
    BCJDEVERS GRAPHIC(10) CCSID 13488 DEFAULT NULL ,
    BCMRGMOD GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    BCMRGOPT GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    BCFFU1 GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    BCFFU2 GRAPHIC(1) CCSID 13488 DEFAULT NULL ,
    BCFNNM GRAPHIC(32) CCSID 13488 DEFAULT NULL ,
    CONSTRAINT COPD900.F98762PD900FA_PK PRIMARY KEY( BCFNNM ) )  
    ;
 
CREATE TABLE COPD900.F98770PD900FA (
    SMFMAT GRAPHIC(10) CCSID 13488 DEFAULT NULL ,
    SMERBLOB BLOB(15728632) DEFAULT NULL ,
    CONSTRAINT COPD900.F98770PD900FA_PK PRIMARY KEY( SMFMAT ) )  
    ;

--  Generate SQL
--  Version:                       V7R1M0 100423
--  Generated on:                  05/08/11 13:27:49
--  Relational Database:           SVR703
--  Standards Option:              DB2 for i
 
CREATE UNIQUE INDEX COPD900.F98306PD900FA_1
    ON COPD900.F98306PD900FA ( PTOBNM ASC , PTPOTP ASC , PTITNUM ASC , PTSQNUM ASC , PTLNGP ASC ) ;
 
CREATE INDEX COPD900.F98306PD900FA_2
    ON COPD900.F98306PD900FA ( PTOBNM ASC , PTLNGP ASC ) ;
 
CREATE INDEX COPD900.F98710PD900FA_1
    ON COPD900.F98710PD900FA ( THTBID ASC , THOBNM ASC ) ;
 
CREATE UNIQUE INDEX COPD900.F98710PD900FA_2
    ON COPD900.F98710PD900FA ( THOBNM ASC ) ;
 
CREATE INDEX COPD900.F98711PD900FA_1
    ON COPD900.F98711PD900FA ( TDTBID ASC , TDDDID ASC , TDOBNM ASC , TDOBND ASC ) ;
 
CREATE INDEX COPD900.F98711PD900FA_2
    ON COPD900.F98711PD900FA ( TDDDID ASC ) ;
 
CREATE INDEX COPD900.F98711PD900FA_4
    ON COPD900.F98711PD900FA ( TDOBNM ASC , TDDDID ASC , TDOBND ASC ) ;
 
CREATE UNIQUE INDEX COPD900.F98711PD900FA_5
    ON COPD900.F98711PD900FA ( TDOBNM ASC , TDOBND ASC ) ;
 
CREATE INDEX COPD900.F98711PD900FA_6
    ON COPD900.F98711PD900FA ( TDOBND ASC ) ;
 
CREATE INDEX COPD900.F98711PD900FA_7
    ON COPD900.F98711PD900FA ( TDOBNM ASC , TDPSEQ ASC ) ;
 
CREATE INDEX COPD900.F98712PD900FA_1
    ON COPD900.F98712PD900FA ( TPTBID ASC , TPINID ASC ) ;
 
CREATE UNIQUE INDEX COPD900.F98712PD900FA_2
    ON COPD900.F98712PD900FA ( TPOBNM ASC , TPINID ASC ) ;
 
CREATE INDEX COPD900.F98713PD900FA_1
    ON COPD900.F98713PD900FA ( TLTBID ASC , TLDDID ASC , TLINID ASC ) ;
 
CREATE INDEX COPD900.F98713PD900FA_2
    ON COPD900.F98713PD900FA ( TLDDID ASC , TLCMPI ASC , TLINID ASC , TLTBID ASC ) ;
 
CREATE INDEX COPD900.F98713PD900FA_3
    ON COPD900.F98713PD900FA ( TLTBID ASC , TLINID ASC , TLCMPI ASC ) ;
 
CREATE INDEX COPD900.F98713PD900FA_4
    ON COPD900.F98713PD900FA ( TLOBNM ASC , TLDDID ASC , TLINID ASC ) ;
 
CREATE INDEX COPD900.F98713PD900FA_5
    ON COPD900.F98713PD900FA ( TLDDID ASC , TLCMPI ASC , TLINID ASC , TLOBNM ASC ) ;
 
CREATE INDEX COPD900.F98713PD900FA_6
    ON COPD900.F98713PD900FA ( TLOBNM ASC , TLINID ASC , TLCMPI ASC ) ;
 
CREATE UNIQUE INDEX COPD900.F98713PD900FA_7
    ON COPD900.F98713PD900FA ( TLOBNM ASC , TLOBND ASC , TLINID ASC ) ;
 
CREATE INDEX COPD900.F98720PD900FA_1
    ON COPD900.F98720PD900FA ( BVBVID ASC ) ;
 
CREATE UNIQUE INDEX COPD900.F98720PD900FA_2
    ON COPD900.F98720PD900FA ( BVOBNM ASC ) ;
 
CREATE INDEX COPD900.F98740PD900FA_1
    ON COPD900.F98740PD900FA ( ELPRDTYP ASC , ELAPPLID ASC , ELFORMID ASC , ELCTRLID ASC , ELWEVENT ASC , ELERID3 ASC ) ;
 
CREATE INDEX COPD900.F98740PD900FA_2
    ON COPD900.F98740PD900FA ( ELOBNM ASC , ELPRDTYP ASC , ELFORMID ASC , ELCTRLID ASC , ELWEVENT ASC , ELERID3 ASC ) ;
 
CREATE UNIQUE INDEX COPD900.F98740PD900FA_3
    ON COPD900.F98740PD900FA ( ELPRDTYP ASC , ELOBNM ASC , ELVERS ASC , ELFMNM ASC , ELCTRLID ASC , ELWEVENT ASC , ELERID3 ASC ) ;
 
CREATE INDEX COPD900.F98740PD900FA_4
    ON COPD900.F98740PD900FA ( ELPRDTYP ASC , ELOBNM ASC , ELFMNM ASC , ELCTRLID ASC , ELWEVENT ASC , ELERID3 ASC ) ;
 
CREATE INDEX COPD900.F98741PD900FA_1
    ON COPD900.F98741PD900FA ( ESEVSPEC ASC , ESEVSEQ ASC ) ;
 
CREATE UNIQUE INDEX COPD900.F98741PD900FA_2
    ON COPD900.F98741PD900FA ( ESEVSK ASC , ESEVSEQ ASC ) ;
 
CREATE INDEX COPD900.F98743PD900FA_1
    ON COPD900.F98743PD900FA ( DTTMPLID ASC , DTTMPLNM ASC ) ;
 
CREATE INDEX COPD900.F98743PD900FA_2
    ON COPD900.F98743PD900FA ( DTOBNM ASC , DTTMPLTYP ASC , DTTMPLID ASC ) ;
 
CREATE UNIQUE INDEX COPD900.F98743PD900FA_3
    ON COPD900.F98743PD900FA ( DTOBNM ASC ) ;
 
CREATE UNIQUE INDEX COPD900.F98745PD900FA_1
    ON COPD900.F98745PD900FA ( SFOBNM ASC , SFNMNAME ASC ) ;
 
CREATE INDEX COPD900.F98750PD900FA_1
    ON COPD900.F98750PD900FA ( FTAPPLID ASC , FTTEXTID ASC , FTLNGP ASC , FTSY ASC ) ;
 
CREATE UNIQUE INDEX COPD900.F98750PD900FA_2
    ON COPD900.F98750PD900FA ( FTOBNM ASC , FTTEXTID ASC , FTLNGP ASC , FTSY ASC ) ;
 
CREATE INDEX COPD900.F98751PD900FA_1
    ON COPD900.F98751PD900FA ( FSAPPLID ASC , FSRCRDTP ASC , FSGNCID1 ASC , FSGNCID2 ASC , FSWEVENT ASC , FSGNCID3 ASC ) ;
 
CREATE INDEX COPD900.F98751PD900FA_2
    ON COPD900.F98751PD900FA ( FSOBNM ASC , FSRCRDTP ASC , FSGNCID1 ASC , FSGNCID2 ASC , FSWEVENT ASC , FSGNCID3 ASC ) ;
 
CREATE INDEX COPD900.F98751PD900FA_3
    ON COPD900.F98751PD900FA ( FSOBNM ASC , FSRCRDTP ASC , FSGNCID1 ASC , FSCTRLID ASC ) ;
 
CREATE UNIQUE INDEX COPD900.F98751PD900FA_4
    ON COPD900.F98751PD900FA ( FSOBNM ASC , FSRCRDTP ASC , FSFMNM ASC , FSGNCID2 ASC , FSWEVENT ASC , FSGNCID3 ASC ) ;
 
CREATE INDEX COPD900.F98751PD900FA_5
    ON COPD900.F98751PD900FA ( FSOBNM ASC , FSRCRDTP ASC , FSFMNM ASC , FSCTRLID ASC ) ;
 
CREATE INDEX COPD900.F98751PD900FA_6
    ON COPD900.F98751PD900FA ( FSRCRDTP ASC ) ;
 
CREATE INDEX COPD900.F98752PD900FA_1
    ON COPD900.F98752PD900FA ( AHAPPLID ASC ) ;
 
CREATE UNIQUE INDEX COPD900.F98752PD900FA_2
    ON COPD900.F98752PD900FA ( AHOBNM ASC ) ;
 
CREATE INDEX COPD900.F98753PD900FA_1
    ON COPD900.F98753PD900FA ( ADAPPLID ASC , ADFRMID ASC ) ;
 
CREATE INDEX COPD900.F98753PD900FA_2
    ON COPD900.F98753PD900FA ( ADFRMID ASC ) ;
 
CREATE UNIQUE INDEX COPD900.F98753PD900FA_3
    ON COPD900.F98753PD900FA ( ADOBNM ASC , ADFMNM ASC ) ;
 
CREATE UNIQUE INDEX COPD900.F98753PD900FA_4
    ON COPD900.F98753PD900FA ( ADFMNM ASC ) ;
 
CREATE INDEX COPD900.F98760PD900FA_1
    ON COPD900.F98760PD900FA ( RTREPORTID ASC , RTTEXTID ASC , RTLNGP ASC , RTSY ASC ) ;
 
CREATE INDEX COPD900.F98760PD900FA_2
    ON COPD900.F98760PD900FA ( RTOBNM ASC , RTTEXTID ASC , RTLNGP ASC , RTSY ASC ) ;
 
CREATE UNIQUE INDEX COPD900.F98760PD900FA_3
    ON COPD900.F98760PD900FA ( RTOBNM ASC , RTVERS ASC , RTTEXTID ASC , RTLNGP ASC , RTSY ASC ) ;
 
CREATE INDEX COPD900.F98761PD900FA_1
    ON COPD900.F98761PD900FA ( RSREPORTID ASC , RSRCRDTP ASC , RSGNCID1 ASC , RSGNCID2 ASC , RSWEVENT ASC , RSGNCID3 ASC ) ;
 
CREATE INDEX COPD900.F98761PD900FA_2
    ON COPD900.F98761PD900FA ( RSOBNM ASC , RSRCRDTP ASC , RSGNCID1 ASC , RSGNCID2 ASC , RSWEVENT ASC , RSGNCID3 ASC ) ;
 
CREATE INDEX COPD900.F98761PD900FA_3
    ON COPD900.F98761PD900FA ( RSOBNM ASC , RSRCRDTP ASC , RSGNCID1 ASC , RSCTRLID ASC ) ;
 
CREATE UNIQUE INDEX COPD900.F98761PD900FA_4
    ON COPD900.F98761PD900FA ( RSOBNM ASC , RSVERS ASC , RSRCRDTP ASC , RSGNCID1 ASC , RSGNCID2 ASC , RSWEVENT ASC , RSGNCID3 ASC ) ;
 
CREATE INDEX COPD900.F98761PD900FA_5
    ON COPD900.F98761PD900FA ( RSOBNM ASC , RSVERS ASC , RSRCRDTP ASC , RSGNCID1 ASC , RSCTRLID ASC ) ;
 
CREATE INDEX COPD900.F98761PD900FA_6
    ON COPD900.F98761PD900FA ( RSRCRDTP ASC ) ;
 
CREATE INDEX COPD900.F98762PD900FA_1
    ON COPD900.F98762PD900FA ( BCBHVRID ASC ) ;
 
CREATE INDEX COPD900.F98762PD900FA_2
    ON COPD900.F98762PD900FA ( BCSRCFNM ASC , BCBHVRID ASC ) ;
 
CREATE INDEX COPD900.F98762PD900FA_3
    ON COPD900.F98762PD900FA ( BCOBNM ASC , BCBHVRID ASC ) ;
 
CREATE INDEX COPD900.F98762PD900FA_4
    ON COPD900.F98762PD900FA ( BCOBNM ASC , BCSRCFNM ASC ) ;
 
CREATE UNIQUE INDEX COPD900.F98762PD900FA_5
    ON COPD900.F98762PD900FA ( BCFNNM ASC ) ;
 
CREATE UNIQUE INDEX COPD900.F98762PD900FA_6
    ON COPD900.F98762PD900FA ( BCSRCFNM ASC , BCFNNM ASC ) ;
 
CREATE UNIQUE INDEX COPD900.F98762PD900FA_7
    ON COPD900.F98762PD900FA ( BCOBNM ASC , BCFNNM ASC ) ;

/*Use the below statement to generate the INSERT INTO SELECT * FROM statements*/

select 'INSERT INTO COPD900.' || table_name || 'PD900FA SELECT * FROM COPD900.' || table_name || ';' from qsys2.systables where table_schema = 'COPD900' and length(table_name) < 7 ;

INSERT INTO COPD900.F98306PD900FA SELECT * FROM COPD900.F98306;   
INSERT INTO COPD900.F98710PD900FA SELECT * FROM COPD900.F98710;   
INSERT INTO COPD900.F98711PD900FA SELECT * FROM COPD900.F98711;   
INSERT INTO COPD900.F98712PD900FA SELECT * FROM COPD900.F98712;   
INSERT INTO COPD900.F98713PD900FA SELECT * FROM COPD900.F98713;   
INSERT INTO COPD900.F98720PD900FA SELECT * FROM COPD900.F98720;   
INSERT INTO COPD900.F98740PD900FA SELECT * FROM COPD900.F98740;   
INSERT INTO COPD900.F98741PD900FA SELECT * FROM COPD900.F98741;   
INSERT INTO COPD900.F98743PD900FA SELECT * FROM COPD900.F98743;   
INSERT INTO COPD900.F98745PD900FA SELECT * FROM COPD900.F98745;   
INSERT INTO COPD900.F98750PD900FA SELECT * FROM COPD900.F98750;   
INSERT INTO COPD900.F98751PD900FA SELECT * FROM COPD900.F98751;   
INSERT INTO COPD900.F98752PD900FA SELECT * FROM COPD900.F98752;   
INSERT INTO COPD900.F98753PD900FA SELECT * FROM COPD900.F98753;   
INSERT INTO COPD900.F98760PD900FA SELECT * FROM COPD900.F98760;   
INSERT INTO COPD900.F98761PD900FA SELECT * FROM COPD900.F98761;   
INSERT INTO COPD900.F98762PD900FA SELECT * FROM COPD900.F98762;