Thursday, 8 May 2014

oracle template for de-duplication dedupe

This is an often fought with concept.  You need to create a PK over a table based upon a list of columns, but it’s hard to effectively identify which row to delete, because they have the same key value.  Now please remember, I’m considering these rows to be duplicates, and that I don’t really care which one I keep and which one I delete.

My situation is quite specific, so I’ll elaborate.  One of my table conversions is falling back to RAT because of duplicates in the F4072TEM based upon the new PK that is in the new F4072.  Darn it…  I cannot afford to have this go back to RAT.  What happens when something is RAT – the dupes are just chucked away anyway….  So, I’m going get rid of them with some set based logic.

Desc the PK, and you get a list of columns:

ADITM, ADAST, ADAN8, ADIGID, ADCGID, ADOGID, ADCRCD, ADUOM, ADMNQ, ADEXDJ, ADUPMJ, ADTDAY

So, use this list of columns in the template SQL below:

delete from tablename
where (rowid,COLUMN LIST) in (
select max(rowid), COLUMN LIST
from tablename
group by COLUMN LIST
having count(1) > 1);

To create

delete from proddta.f4072tem
where (rowid,ADITM, ADAST, ADAN8, ADIGID, ADCGID, ADOGID, ADCRCD, ADUOM, ADMNQ, ADEXDJ, ADUPMJ, ADTDAY) in (
select max(rowid), ADITM, ADAST, ADAN8, ADIGID, ADCGID, ADOGID, ADCRCD, ADUOM, ADMNQ, ADEXDJ, ADUPMJ, ADTDAY
from proddta.f4072tem
group by ADITM, ADAST, ADAN8, ADIGID, ADCGID, ADOGID, ADCRCD, ADUOM, ADMNQ, ADEXDJ, ADUPMJ, ADTDAY
having count(1) > 1);

Nice, it deletes the newest record, you could use min to delete the oldest record.

Wait… Update…  The above is good if there is only every 1 other duplicate.  When there are a lot of possible duplicates that match the key, then the above will have to be run multiple times, which is not good…  unless you put some recursion on your scripts, so…

Although quite a bit slower, the following works:  thanks to http://www.dba-oracle.com/t_delete_duplicate_table_rows.htm for some help on this one.

delete from proddta.f4072tem t1
where t1.rowid >
ANY (
select
t2.rowid
from proddta.f4072tem t2
WHERE t1.ADITM = t2.ADITM
and t1.ADAST = t2.ADAST
and t1.ADAN8 = t2.ADAN8
and t1.ADIGID = t2.ADIGID
and t1.ADCGID = t2.ADCGID
and t1.ADOGID = t2.ADOGID
and t1.ADCRCD = t2.ADCRCD
and t1.ADUOM = t2.ADUOM
and t1.ADMNQ = t2.ADMNQ
and t1.ADEXDJ = t2.ADEXDJ
and t1.ADUPMJ = t2.ADUPMJ
and t1.ADTDAY = t2.ADTDAY);

And finally…  another one.  The above is way too slow, I’m not too sure that it’ll ever return, so: my final instalment:  I really must pay homage to burleson consulting, their website is gold when it comes to quick oracle tips.  http://www.dba-oracle.com/ 

delete from proddta.F4072tem where rowid in
  (
  select "rowid" from
     (select "rowid", rank_n from
         (select rank() over (partition by  ADITM, ADAST, ADAN8, ADIGID, ADCGID, ADOGID, ADCRCD, ADUOM, ADMNQ, ADEXDJ, ADUPMJ, ADTDAY order by rowid) rank_n, rowid as "rowid"
             from proddta.F4072tem
             where (ADITM, ADAST, ADAN8, ADIGID, ADCGID, ADOGID, ADCRCD, ADUOM, ADMNQ, ADEXDJ, ADUPMJ, ADTDAY) in
                (select ADITM, ADAST, ADAN8, ADIGID, ADCGID, ADOGID, ADCRCD, ADUOM, ADMNQ, ADEXDJ, ADUPMJ, ADTDAY from proddta.F4072tem
                  group by ADITM, ADAST, ADAN8, ADIGID, ADCGID, ADOGID, ADCRCD, ADUOM, ADMNQ, ADEXDJ, ADUPMJ, ADTDAY
                  having count(*) > 1
                )
             )
         )
     where rank_n > 1
  );

excel convert columns to rows or rows to columns

This may be the best thing that I’ve discovered in the last 10 years.  How many times have I had a list of columns that I wanted to convert to rows and vise-versa?  too many…  The common problems is “SELECT * FROM F0101” which goes across the page, and I want it down the page.

The solution is simple.

my classic example is here:

Truncate table PRODDTA.F4072TEM ;

INSERT INTO PRODDTA.F4072TEM (ADAST, ADITM, ADLITM, ADAITM, ADAN8, ADICID, ADSDGR, ADSDV1, ADSDV2, ADSDV3, ADCRCD, ADUOM, ADMNQ, ADEFTJ, ADEXDJ, ADBSCD, ADLEDG, ADFRMN, ADFVTR, ADFGY, ADATID, ADURCD, ADURDT, ADURAT, ADURAB, ADURRF, ADUSER, ADPID, ADJOBN, ADUPMJ, ADTDAY, ADIGID, ADCGID, ADOGID, ADPARTFG) SELECT ADAST, ADITM, ADLITM, ADAITM, ADAN8, ADICID, ADSDGR, ADSDV1, ADSDV2, ADSDV3, ADCRCD, ADUOM, ADMNQ, ADEFTJ, ADEXDJ, ADBSCD, ADLEDG, ADFRMN, ADFVTR, ADFGY, ADATID, ADURCD, ADURDT, ADURAT, ADURAB, ADURRF, ADUSER, ADPID, ADJOBN, ADUPMJ, ADTDAY, 0, 0, 0, ' ' FROM PRODDTA.F4072

I have all of the columns from this create table statement, but I want them down the page, not across.

So, I paste the convents into a spreadsheet and use DATA –> text to columns and I use the comma as the deliminator, so I have all of the columns across the screen:

image

Then paste special

image

Oh wow, life is good!!!

image

This’ll work both ways too (said the vicar to the nun!)

Wednesday, 7 May 2014

e1 TCs and run local

I’m trying to change a dodgy record that wants to run R894072 locally and not on the server. This is a real pain, as when things are run locally – it messes with all of the concurrency and queuing.  I find that when you have a mix of local and server run jobs, there is a bit more waiting around.

So I tried going into the plan and changing the 1 for run local to 0, but this would never save, what a pain.

Then I do some looking about and find P98430.

image

This allows you to update the master and then the pesky TC won’t run ion the deployment server.  That is handy!

Monday, 5 May 2014

Using heterogeneous database services to connect to AS/400 from oracle.

 

Introduction:

Oracle have a great tool that allows you to treat ODBC as a database link. Note that it can only be used as a database link, not as a database that you can log into. This seems like useless information now, but it’ll make sense in a little while.

clip_image002

Why would you want to do this? Well there are about 100000 reasons, but being able to use a central SQL editor to write any SQL across any database is a pretty good start.

image

Classic situation of reading right to left:

· A client connects to the database and requests data from the AS/400 via a database link

· The database link points to a tnsnames.ora entry that MUST be right in the databases tnsnames.ora on the database server. Note that this is not on your client, it’s server to server comms. Note also that tns defines this as a heterogeneous service (HS=) in the DESCRIPTION.

· The tnsnames.ora entry above points to a listener for the gateway SID that is referenced in the database link. In my case, this is the specific listener that I’ve defined in my second oracle home for the gateway.

· The gateway listener has defined the SID in question (HS based) to use dg4odbc to serve up the data. It also defines an oracle home where the program is going to come from.

· Finally this gateway program uses it’s initDIS.ora file in the HS subdirectory to find the system based DSN for oracle to read to find the data. This is what points the oracle listener to the generic ODBC name.

· ODBC then handles the rest!

That is a lot of moving parts!

To get much quicker data transfers between AS/400 and oracle than R98403 – well, that is another totally valid reason.

Software / hardware situation:

· Existing Oracle 64 bit 11GR2 install

· Windows 2008R2

· AS/400 V5R4

· Need to move data from AS/400 to oracle

Steps:

Step1 : ODBC

You’ll need a 64bit ODBC system DSN – easy.

clip_image006

Remember this is in %windows%\system32\

clip_image008

You can set up what ever default libraries you want, when you come to use the link, you can pretty much select data from any library.

Step 2: Install gateway services

You’ll need oracle gateway services installed (not so easy), so you need an additional oracle home with additional software installed. Note that the gateway for ODBC does NOT require any additional licensing (What!! This is oracle!!!) So this additional oracle home will have a new listener, we’ll use this for the comms to the HS (heterogenous service).

clip_image010

clip_image012

D:\downloads\software\Oracle Database 11G R2 (11.2.0.3) x64 Windows\p10404530_112030_MSWIN-x86-64_1of7\gateways\stage\products.xml

I installed from a location that is similar to the above.

Note also that there is nothing to start, expect for the listener. No database instance to start.

Step 4: Create additional listener

As mentioned about, an additional listener

D:\product\11.2.0\tg_1\NETWORK\ADMIN\listener.ora

# listener.ora Network Configuration File: D:\product\11.2.0\tg_1\network\admin\listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER_GW =

(SID_DESC =

(SID_NAME = A01PRODDTA)

(ORACLE_HOME = D:\product\11.2.0\tg_1)

(PROGRAM = dg4odbc)

)

LISTENER_GW =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = DBserver.com)(PORT = 1522))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))

)

)

ADR_BASE_LISTENER_GW = D:\product\11.2.0\tg_1

Start and stop your new listener

lsnrctl start LISTENER_GW

lsnrctl stop LISTENER_GW

Step 5: Define HS instance that listener connects to

You’ll need to configure the HS data source which the listener will connect to.

initA01PRODDTA.ora

D:\product\11.2.0\tg_1\hs\admin

# This is a sample agent init file that contains the HS parameters that are

# needed for the Database Gateway for ODBC

#

# HS init parameters

#

HS_FDS_CONNECT_INFO=A01PRODDTA

HS_FDS_TRACE_LEVEL=NO

#

# Environment variables required for the non-Oracle system

#

#set <envvar>=<value>

Note that my HS INSTANCE is called A01PRODDTA

Step 6: tnsnames.ora

You’ll need to configure tnsnames on your database server, YES!! Because the database link is on the server and it needs to be right for your SQL to work (this took my slow mind some time to understand).

D:\Oracle\product\11.2.0\dbhome\NETWORK\ADMIN\tnsnames.ora

A01PRODDTA =

(DESCRIPTION =

(ADDRESS=(PROTOCOL=TCP) (HOST=NZAKLEVFN739.zeus.ghsewn.com) (PORT=1522))

(CONNECT_DATA=(SID=A01PRODDTA))(HS=))

Note that the syntax of the above is VITAL.

Step 7: Create DB link

This is simple, but must be a DB link.

clip_image014

Some errors along the way

The above was what I got wrong for many hours, and not knowing where it was going wrong. I got about 100000 of these:

D:\product\11.2.0\tg_1\hs\trace\A01PRODDTA_agt_47444.trc

Oracle Corporation --- MONDAY MAY 05 2014 12:11:48.283

Heterogeneous Agent Release

11.2.0.3.0

HS Agent diagnosed error on initial communication,

probable cause is an error in network administration

Network error 2: NCR-00002: NCR: Invalid usage

HS Gateway: NULL connection context at exit

dg4odbc ORA-28546: connection initialization failed, probable Net8 admin error

All of the above was because my tnsnames.ora was not right. This is the one that the main database uses in the home\network\admin for the database which you created the link in (not what you are pointing to with the link).

Force parallel for large DDL / DML

If you are about to run a large script, gen indexes, create tables as select, etc…  Make sure that you execute something like below before you begin.

This will mean that you don’t need to add parallel at the end of the all the scripts, because your session with have it set.  Perhaps no 32, but if you have 32 procs  - go for it!

ALTER SESSION FORCE PARALLEL DDL PARALLEL 32;
ALTER SESSION FORCE PARALLEL DML PARALLEL 32;
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 32;

Friday, 2 May 2014

give me the size of data and indexes for a file in an oracle database please

I know that I’ve posted before on a similar topic.  This is slightly different.  Say you want to find the SIZE of your F0911 and the size of it’s indexes (generally more than the data I might add).  If you log in as the owner of the table (PRODDTA) for example, you can use this.

This will give the total in MB, mega bytes.

Data:

select sum(bytes)/1024/1024, 'f0911' from user_segments where segment_name = 'F0911' ;

Indexes:

select sum(bytes)/1024/1024, 'f0911index' From user_segments where segment_name like 'F0911\_%'  ESCAPE '\' and segment_type = 'INDEX' ;

The only cool this is the use of ESCAPE to escape the _, which generally indicates ANY one character in oracle.  This is how you can look for either % or _ as a literal in a string with a like statement.

White Paper of JD Edwards load testing

I’ve been involved with the creation of this white paper on load testing in JD Edwards, take a look.

http://elink.myriad-it.com/m/1/21377861/b12114-49fa7c4a-fbb2-4142-afb2-804a5c2864e5/1/161/f1d41714-4368-420a-92db-392eb662c6ef

Shows some really good use-case scenarios.