oracle to jde date conversion

CREATE OR REPLACE function PRODDTA.jde_date_to_char

(           jde_date            number,

            format_str          varchar2 := 'DD/MM/YYYY'

)

return varchar2

is

begin

/* Function to convert dates in JDE to format specified */

    return

            to_char( jde_date_to_date(jde_date), format_str    );

end jde_date_to_char;

 

CREATE OR REPLACE function PRODDTA.jde_date_to_date

(     jde_date    number

)

return date

is

begin

/* Function to convert dates in JDE to oracle dates

   JDE stores it dates in a 6 digit number field

   1st 3 characters is the number of years since 1900

   next 3 characters is the day # in the year */

if nvl(jde_date,0) = 0

then

return null;

else

return

to_date(

to_char(

to_number(

substr(to_char(jde_date,'000000'),2,3)

)+1900

)||

substr(to_char(jde_date,'000000'),5,3),

'YYYYDDD'

);

end if;

end jde_date_to_date;

CNC101 - Check specs against physical tables

Run R9698711 to determine which tables are out of synch with what is in the database.  Ripper of a report.  It does report a bunch of crap, because many of the Brazilian tax code tables are not required for Aus, but hey – it’s a start.

Change location of temp PDF files

SERVER JDE.INI

[JDENET] section, please add:

netTemporaryDir=C:\TEMP   (standard windows directory structure)
or
netTemporaryDir=/u03/alternative/path/to/tmp   (Unix based directory structure)

see this

Reg file to stop browser freeze (not brain freeze)

Windows Registry Editor Version 5.00 [HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings]
"MaxConnectionsPerServer"=dword:0000000a
"MaxConnectionsPer1_0Server"=dword:0000000a

Paste the above into a file with a .reg extension and then double click it, it’ll put the correct setting in the users browser.

I’ve made a link to a reg file on my machine above, not too sure what is going to happen on the big bad internet…  Hmm, that answers that…  did not work.

working [LOCALWEB] section of WAS 6.1 JDE.INI

[LOCALWEB]

# Installation flag, if it is 0, no HTML testing setup, disable all HTML testing

AppServerInstalled=1

# Datasource containing serialized specs for web runtime

# Used for synchonization of specs through JDB

Spec Datasource=OneWorld Local - DV812

# Name of local web server, localhost is default but may not be valid always.

webhostname=localhost

# web server port , Websphere Express default is 7080

webport=9080

# executable for starting web server

webserverstart=C:\IBM\WebSphere\AppServer\profiles\AppSrv01\bin\startServer.bat

# executable for stopping web server

webserverstop=C:\IBM\WebSphere\AppServer\profiles\AppSrv01\bin\stopServer.bat

# arguments for starting web server

webserverstartarg=server1

# arguments for stopping web server

webserverstoparg=server1

# start web server on demand, or immediately

# valid values : ONDEMAND (web server will be started on the first HTTP request) ,

                 MANUAL (web server has to be started manually by user on port specified),

                 IMME (web server starts as soon as ActivConsole starts)

StartAppServer=IMME

# delay time between starting web server and launching browser window

# default value is 60 (60 secs)

WebDelay=60

What version of WAS are you running

You’ll find a program called “genVersionReport.bat”, this will generate an HTML file with all of the version information that you could ever dream of “IBM WebSphere Application Server Product Installation Status Report”.  You can use this on the express version and the full version.  By default, the report is generated in the directory that you ran the program in.  The file is called versionReport.html

Dodging up a package

No time to build a package, well try this on for size…

All you need to to is copy all of the package name files from central objects, copy the serialized objects and generate a manifest – job done!

So in my little example below, I want to get all CRP code (PY812) that is in package PY812CF into PD812CF.  Now, I’m not that much of a cowboy, I’m actually in a sandpit environment which is a exact copy of PROD – honest…  No breach of change control here…  Noooo sirrrreeee.

Remember the goal…  The goal is to leave all of the metadata tables thinking that the same old package is deployed – you are just changing all of the code beneath…  The perfect crime!

The @ syntax at the end of the table name is a reference to a database link.

I truncate all of the existing PD812CF package central objects.  Don’t truncate the F98770 table, it’s the package manifest.

select 'INSERT INTO PD812.' || table_name ||' SELECT * FROM PY812.' || substr(table_name,1,6) || 'PY812CF@jde_d2jde ;'
from all_tables
where owner = 'PD812'
and table_name like '%CF' and table_name not like '%F98770%'

Leave the F98770, this is the manifest.

I then copy the F989999 and F989998 over, but you could let them JITI.

Log into the generation machine with WebDevelopment=TRUE (see one of my older posts) and generate the manifest.

Job done.  You might want to copy the enterprise server binaries over too, just to be sure.

If you are lucky enough to have oracle enterprise edition

There are a couple of simple words that you can type at the end of many SQL queries and DML statements that can imrpove performance dramatically…  parrallel #number.

My example is:

CREATE INDEX &&DATAOWNER..F03B21_CO ON &&DATAOWNER..F03B21
(ZSCO)
LOGGING
TABLESPACE &&DATAOWNER.I
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
PARALLEL 3 COMPUTE STATISTICS;

This will create 3 additional worker threads that will beaver away at generating the index.

If you have large tables in JDE, you do NOT want to generate indexes through the OMW option “generate indexes”.  This will take too long.  You want to generate the table against a datasource that you do not use (Data Dict for example) with logging on.

From your fat boy command line, run something like:

findstr “CREATE “ jde.log > CREATESTATEMENTS.sql

Then massage all of the CREATE INDEX statements to include the parallel option.

Remember that generating indexes from JDE for large tables is bad… It will generate all of them as use all of the defaults for size, therefore you’ll end up with a pile of badly fragmented indexes that took WAY TOO LONG to generate.

Note that you need to have enterprise edition to use this (well, actually I think that you might be able to specify the parameter, but it’ll be recorded in the DB as a infringement).

Operations That Can Be Parallelized

Oracle can parallelize operations that involve processing an entire table or an entire partition. These operations include:

• SQL queries requiring at least one full table scan or queries involving an index range scan spanning multiple partitions.

• Operations such as creating or rebuilding an index or rebuilding one or more partitions of an index.

• Partition operations such as moving or splitting partitions

• CREATE TABLE AS SELECT operations, if the SELECT involves
  a full table or partition scan.

• INSERT INTO . . . SELECT operations, if the SELECT involves a full table or partition scan.

• Update and delete operations on partitioned tables

 

Some of this was taken from http://www.akadia.com/services/ora_parallel_processing.html