MSDE Logging, did you know?

C:\Program Files\Microsoft SQL Server\Mssql$JDELocal\DataMSSQL$JDELOCAL\LOG contains a file called “errorlog”, which contains MSDE error messages.  That is pretty handy…  I was getting:

5788/3884 MAIN_THREAD                           Wed Jan 27 16:02:29.590001    Odbc_u1.c901
    ODB0000164 - STMT:00 [42000][945] [Microsoft][ODBC SQL Server Driver][SQL Server]Database 'SPEC_PY812BF' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.

I stopped and started the JDELOCAL MSDE service and everything came up like a peach – but good to have a look through those logs.

I was getting this in the errorlog file:

2010-01-27 14:54:54.83 spid52    Starting up database 'SPEC_PY812BF'.
2010-01-27 14:54:54.83 spid52    udopen: Operating system error 2(error not found) during the creation/opening of physical device d:\e812\PY812\Spec\SPEC_PY812BF.mdf.
2010-01-27 14:54:54.83 spid52    FCB::Open failed: Could not open device d:\e812\PY812\Spec\SPEC_PY812BF.mdf for virtual device number (VDN) 1.

Find missing indexes and generate DDL for them

 

select 'SELECT DBMS_METADATA.GET_DDL(''INDEX'',''' || index_name || ''',''PRODDTA'') FROM DUAL;'
from all_indexes t1
where t1.owner = 'PRODDTA'
and not exists (select 1
from all_indexes t2
where t2.index_name = t1.index_name and t2.owner = 'UADTA')
/

Oh where oh where could my archive files be? Oh where oh where could they be

At the SQL prompt:

show parameter archive

Woot!

Create DDL / DML for INDEX or TABLE or anything!!!

 

Want to get all of the DML / DDL for indexes or tables?  Here is the INDEX example below.  Just remove more from the end WHERE clause to have more DML. 

SELECT 'SELECT DBMS_METADATA.GET_DDL(''' || 'INDEX''' || ',''' || index_name || '''' || ',''' || 'PD812' || ''')'  || ' FROM dual ;'
FROM all_indexes where owner = 'PRODDTA' and table_name = 'F0911'

Spool the results to a file and add the following to the header of the file:

set heading off
set feedback off
set long 99999
set pages 0
set heading off
set lines 1000
set wrap on
set linesize 100
spool c:\temp\F0911DML.sql

SELECT DBMS_METADATA.GET_DDL('INDEX','F0911_11','PRODDTA') FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F0911_12','PRODDTA') FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F0911_13','PRODDTA') FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F0911_15','PRODDTA') FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F0911_17','PRODDTA') FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F0911_18','PRODDTA') FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F0911_19','PRODDTA') FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F0911_23','PRODDTA') FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F0911_24','PRODDTA') FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F0911_25','PRODDTA') FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F0911_27','PRODDTA') FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F0911_28','PRODDTA') FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F0911_29','PRODDTA') FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F0911_30','PRODDTA') FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F0911_32','PRODDTA') FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F0911_33','PRODDTA') FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F0911_34','PRODDTA') FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F0911_35','PRODDTA') FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F0911_36','PRODDTA') FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F0911_37','PRODDTA') FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F0911_0','PRODDTA') FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F0911_6','PRODDTA') FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F0911_8','PRODDTA') FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F0911_9','PRODDTA') FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F0911_10','PRODDTA') FROM dual ;
spool off;

Wow, run the script above and you will have all of the “CREATE INDEX” statements.  Note that the initial extents might not be too great (mine were all standard).

UBEs say done – but blank report

Nothing in the jde.log file for the UBE – but the report is always blank.

Checked out the logs dir and found that the metadata kernel was spitting out logs like:

12796/11260 WRK:Metadata job 14479               Thu Jan 21 15:17:06.025001                   SpecMisc.c3125

            copyER() - jdeSpecFetch() failed with error code, JDESPECRESULT_JAVAEXCEPTION.  Record was selected from repository using the key, a664c624-e6ca-457b-ae0f-412a0275aa68.

12796/11260 WRK:Metadata job 14479               Thu Jan 21 15:17:06.025002                   SpecMisc.c2702

            copyBetweenRepositories() - Failure to copy GBRSPEC/GBRLINK records for report/version R5542565NI_+

12796/11260 WRK:Metadata job 14479               Thu Jan 21 15:17:06.431000                   ServerDispatch.cpp586

            Spec Cache creation for R5542565NI Failed.

12796/11260 WRK:Metadata job 14479               Thu Jan 21 15:17:06.431001                   ServerDispatch.cpp639

            Failed to create job cache (job = [1], report/version = R5542565NI - ANDM002).

After seeing this, I looked at the system/bin32 dir which has stdout for the java process that runs in the metadata kernel, it had:

21 Jan 2010 13:54:03,480 [SEVERE]  - [12796][METADATA]        getInternalCStructFromXML Error, Exception. java.lang.OutOfMemoryError

21 Jan 2010 15:08:48,034 [SEVERE]  - [12796][METADATA]        getInternalCStructFromXML Error, Exception. java.lang.OutOfMemoryError

21 Jan 2010 15:13:08,717 [SEVERE]  - [12796][METADATA]        getInternalCStructFromXML Error, Exception. java.lang.OutOfMemoryError

21 Jan 2010 15:17:06,025 [SEVERE]  - [12796][METADATA]        getInternalCStructFromXML Error, Exception. java.lang.OutOfMemoryError

So – we are out of memory!

Looking at kernel 12797, it’s using 204 Mb of mem,. Which is not excessive

But the default max size for the JVM is 128M

The server JDE.INI can have the following set int increase the size of the JVM:

DefaultOptions=-Xms128m -Xmx256m

XMLPVMOPTIONS=-Djava.compiler=NONE -Xmx256m

MetadataVMOptions=-Xmx256m

A quick fix is a restart!

long passwords, LDAP JDE WAS JAS etc

If you’ve got this far, you know what I’m talking about!

We’ve got a POC running with WAS6, collaborative portal and long passwords signing into JDE.  This is all on 8.98.1.1, of course this is for WAS portal 6.0 because 6.1 was only supported with 8.98.2.

Long usernames is next, a couple of changes to the pumaservice.properties and WAS config and we should be cooking with GAS.  Oh yeah, there are some changes with LDAP too, but it’s possible.

What is the format of a JDE shortcut?

HREF="HTTP://webservername:81/jde/servlet/com.jdedwards.runtime.shortcut.ShortcutLauncher?scEnc=4f1d65346f336c1420236f4872336510435e6d40615f791253496607770272234f0165256f1f6c5920046f4572526510435e6d42612c7906531c6617772572044f1a65366f346c0b20216f187202655443526d4161127977535f6645775172544f5c65156f0e6c55202f6f4772456553430c6d34610f7954530e6649771d72434f1d65346f246c0120216f06720a654f43016d4d6134796a532b6631775172554f5e6566"

Work With Addresses

Quickly enable logging

Use the following SQL to quickly update logging to be enabled.

Note that this statement will enable logging for PY(ish) environments for the F0011.

update sy812.f9500001
set frffcfr5 = '1'
where frobjnm = 'F0011' and frenvironmnt like '%PY812%' ;

commit ;

You need to do this because auditing is sometimes a real pain in the ass. It thinks it's off in the catalogs, but maybe after a data refresh, it's actually on... You know what I mean if you got to here!