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)
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.
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.
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.
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.
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.
SELECT convert(nvarchar(4000),convert(varbinary(4000),ftedata)) from SY900.F90712 order by FTEVNTTIME desc;
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!
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") processesRETVAL=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 forkecho $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
firead 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>&1if [ `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
esacexit $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.
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;
I know this might not be the best forum for this, but I need help in Melbourne. Come and join the best and most experienced CNC team in Australia. If you or anyone would be interested, please get in contact with me. Use http://www.myriad-it.com/index.php?option=com_aicontactsafe&view=message&layout=message&pf=1&Itemid=96 to send me your details, or set up a phone call.
We can arrange sponsorship if needed, so don’t let your location get in the way of this opportunity.
We’ll be testing your CNC & JDE knowledge, so only experienced people should apply.
