Awesome problem… UBE ending early on AS/400 not completing all records in main select

The benefits of ODBC trace are still there when debugging UBEs especially.

So this was the problem.  There was a main section with a SQL statement that was based on a VIEW (this is important) and should have processed 20000 rows.  The SQL was good and tidy, but the batch job would stop after 10 rows on the 400 and 271 on the client.  Yep, that is weird.  The problem was kind of intermittent, but consistent…  (Even that is a bad description).

The developers were saying the server was rubbish and fix it.  This is a tough situation, as CNC is generally at the bottom of these types of problems that are in the nether regions of peoples responsibilities.

So we ran it with logging, nothing really. 

Mar 21 13:31:36.701864 dbdrvsql.c1362  - 1087/139 MAIN_THREAD                          >>>Start - FetchSQLRow QxdaProcessExtDynEDRS

Mar 21 13:31:36.805392 dbdrvsql.c1373  - 1087/139 MAIN_THREAD                          >>>End - FetchSQLRow QxdaProcessExtDynEDRS

Mar 21 13:31:36.805432 dbdrvag.c1143   - 1087/139 MAIN_THREAD                          Exiting PerformRequest, result=-1

Mar 21 13:31:36.805448 jdb_exef.c4618 - 1087/139 MAIN_THREAD                          No More Data found

--UBE--[2]-- End of Records for the section with Return Code (-2147483648) after fetching 0 records.

The above message is a funny one, as this is 2^31 negative.  I think this is the return of an unsigned int if it gets a negative.

Good info, but not helpful enough.

Next level of logging was tracing the job on the 400 and ODBC trace.

ODBC trace turn out to solve the problem quickly.  The ODBC trace has great information about warnings and things that JDE logs ignore.  The ODBC trace shows you accurate handle associates and cursors etc.

What actually was occurring was that there was an update to a parent table in the parent child relationship that the view was based upon. 

So the main loop had a cursor open and that value had been updated with a separate table i/o to the parent record.

This eventually caught up with the code and the report crapped out.  The ODBC trace shows the handles that the errors are reported on.  The different colours show the continuity of the handles.

activConsole    13cc-1e0 EXIT  SQLPrepareW  with return code 0 (SQL_SUCCESS)

                                HSTMT               03AC92F0

                                WCHAR *             0x1BD95F80 [     766] "SELECT T0.AFEDBT, T0.AFEDTN, T0.AFEDLN, T0.AFEDSP, T0.AF57PRID, T0.AF57PRDESC, T0.AFITM, T0.AF57STCD, T0.AFLITM, T0.AF57STDESC, T0.AFAN8, T0.AFALKY, T0.AFTRDJ, T0.AF57ACD, T0.AF57ACOOP, T0.AFAID, T0.AFAID2, T0.AFAID3, T0.AFAID4, T0.AFUSER, T0.AFPID, T0.AFJOBN, T0.AFUPMJ, T0.AFUPMT, T1.BHEDBT, T1.BH57INTF, T1.BHEDCT, T1.BH57SEQ, T1.BH57BSTS, T1.BH57RECC, T1.BH57TOT1, T1.BH57TOT2, T1.BH57TOT3, T1.BH57TOT4, T1.BH57SUM1, T1.BH57SUM2, T1.BH57FDAT, T1.BH57FTIM, T1.BH57TDAT, T1.BH57TTIM, T1.BH57CDAT, T1.BH57CTIM, T1.BH57MDAT, T1.BHUSER, T1.BHPID, T1.BHJOBN, T1.BHUPMJ, T1.BHUPMT, T1.BH57MTIM, T1.BH57EDBT FROM UADTA/F570911F T0,UADTA/F570001 T1 WHERE  (  ( T1.BH57INTF = ? AND T1.BH57BSTS = ? )  )  AND  ( T0.AFEDBT=T1.BHEDBT )  ORDER BY T0.AFEDBT ASC,T0.AF57PRID ASC"

                                SDWORD                   766

activConsole    13cc-1e0 ENTER SQLExecute

                                HSTMT               03AC92F0

activConsole    13cc-1e0 EXIT  SQLExecute  with return code 0 (SQL_SUCCESS)

                                HSTMT               03AC92F0

activConsole    13cc-1e0 ENTER SQLFetch

                                HSTMT               03AC92F0

activConsole    13cc-1e0 ENTER SQLPrepareW

                                HSTMT               03ACAC40

                                WCHAR *             0x2665CF90 [      59] "UPDATE UADTA/F570001  SET BH57BSTS=? WHERE  ( BHEDBT = ? ) "

                                SDWORD                    59

activConsole    13cc-1e0 EXIT  SQLPrepareW  with return code 0 (SQL_SUCCESS)

                                HSTMT               03ACAC40

                                WCHAR *             0x2665CF90 [      59] "UPDATE UADTA/F570001  SET BH57BSTS=? WHERE  ( BHEDBT = ? ) "

                                SDWORD                    59

activConsole    13cc-1e0 ENTER SQLExecute

                                HSTMT               03ACAC40

activConsole    13cc-1e0 EXIT  SQLExecute  with return code 0 (SQL_SUCCESS)

                                HSTMT               03ACAC40

...

activConsole    13cc-1e0 EXIT  SQLFetch  with return code 100 (SQL_NO_DATA_FOUND)

                                HSTMT               03AC92F0

activConsole    13cc-1e0 ENTER SQLCloseCursor

                                SQLHSTMT            03ACB078

activConsole    13cc-1e0 EXIT  SQLCloseCursor  with return code 0 (SQL_SUCCESS)

                                SQLHSTMT            03ACB078

activConsole    13cc-1e0 ENTER SQLCloseCursor

                                SQLHSTMT            03AC92F0

activConsole    13cc-1e0 EXIT  SQLCloseCursor  with return code 0 (SQL_SUCCESS)

                                SQLHSTMT            03AC92F0

activConsole    13cc-1e0 ENTER SQLCloseCursor

                                SQLHSTMT            03ACA3D0

activConsole    13cc-1e0 EXIT  SQLCloseCursor  with return code 0 (SQL_SUCCESS)

                                SQLHSTMT            03ACA3D0

activConsole    13cc-1e0 ENTER SQLCloseCursor

                                SQLHSTMT            03ACA808

activConsole    13cc-1e0 EXIT  SQLCloseCursor  with return code 0 (SQL_SUCCESS)

                                SQLHSTMT            03ACA808

activConsole    13cc-1e0 ENTER SQLCloseCursor

                                SQLHSTMT            03ACAC40

activConsole    13cc-1e0 EXIT  SQLCloseCursor  with return code -1 (SQL_ERROR)

                                SQLHSTMT            03ACAC40

                                DIAG [24000] [IBM][System i Access ODBC Driver]Invalid cursor state. (30022)

activConsole    13cc-1e0 ENTER SQLGetDiagRecW

                                SQLSMALLINT                  1

                                SQLHANDLE           03AB1540

                                SQLSMALLINT                  1

                                SQLWCHAR *          0x2657EEF0 (NYI)

                                SQLINTEGER *        0x2657EEFC

                                SQLWCHAR *          0x2657EF00 (NYI)

                                SQLSMALLINT                256

                                SQLSMALLINT *       0x2657EEE8

2005 script for change object owner

Of course the exact purpose is change object owner, but the framework can be used for any repetitive operations.

-- Change the object owner from
-- PRODDTA to CRPDTA
USE PS_CRP

DECLARE @Object_Name AS varchar(60)

DECLARE curChange_Owner CURSOR READ_ONLY FORWARD_ONLY FOR
    SELECT
        sysusers.name + '.' + sysobjects.name
    FROM
        sysobjects , sysusers
    WHERE
        sysobjects.uid = sysusers.uid
            AND
        sysusers.name = 'PRODDTA'
            AND
        xtype = 'u'

OPEN curChange_Owner

FETCH NEXT FROM curChange_Owner INTO
    @Object_Name

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sp_changeobjectowner @Object_Name, 'CRPDTA'

    FETCH NEXT FROM curChange_Owner INTO
        @Object_Name
END
 
CLOSE curChange_Owner
DEALLOCATE curChange_Owner

Allowing input tray selection with PCL printing in JDE

It is a known issue that you cannot do tray selecting in JDE when your printers are PCL...  Or can you???

Introduction

PCL printing is different to PS, where PCL is all embedded in the file that is sent to the printer, there are no “command line” options as such.  This is why JDE does not give the same flexibility in the PCL printing options as they do for PS.

Dumpbin to find the which DLL has the function you want, and include files.

I’m using the above to find functions with PCL in their text and locating their address:

        950  3B5 00195260 _SetupClientSecurityAPI@4

        951  3B6 00195B40 _SetupClientSecuritySilentAPI@4

       1131  46A 0020C020 _convertPDFToPCL@2524

2164  868 0020C060 _prtFilter_ConvertToPCL@20

So the function that I want has 5 parameters, it’s the last one with  @20.  @4 means one parameter, @8 two etc.

I can find the include files to see the parameters, but that is not important.

KRNL_RTN( int             ) JDEWINAPI prtFilter_ConvertToPCL              (  HUSER                hUser,

JCHAR*               szInPDF,

JCHAR*               szOutLP,

PPRT_PRINTER_INFO    pPRTPrinterInfo,

BOOL                 OutNativeCS             );

Note that the PPRT_PRINTER_INFO has a tray member, but this does not get passed to the final PCF document.

Remember that the JDE “printer definition language” string tells you what DLL and what function is being used to massage your PDF stream into PS or PCL.  I was able to tell the DLL and function name from there also.

Jdekrnl.dll and function _prtFilter_ConvertToPCL@20

PCL background

As stated before,  there is a PCL string at the beginning of a print file that tells the printer what it should do with the stream that is coming at it.  I was able to refer to the following excellent website to find the kind of PCL commands I’d be looking for. http://pcl.to/reference/

I’m expecting that there is a string in my file that specifies which tray the print job needs to go to (Ec&l#H), I want to modify this.  I’m guessing that I will find a complete PCL string somewhere in the function space.

Note that escape is Hex 1B 26 6C (30 39) 48

This sequence could not be found, but I did find all of the other strings, see below:

From above you can see that it’s building the various codes in this block of executable.  You cannot just add code here, you need to be careful what you change.  There are all sort od checks and boundaries that need to be maintained.

The actual output

To see what JDE was doing with the output, I opened the .spl file.  Windows printing (for a print server) writes all of the print files to %SystemRoot%\SYSTEM32\SPOOL\PRINTERS.  So if you pause your printer, you can intercept the output files and see what the heck JDE is putting at the front if the PDF.

The actual PCL file looks like this: C:\winnt\system32\spooler\printer\*.SHD (printer info) and .SPL (file from JDE)

Note this is it in colour.

%-12345XE&l1X&u300D&l26A&l1O&l0E&l0L*p0x0Y*c8418x5952Y*c0T&k2G

*r3U

(s1p7.00v0s3b16602T*p2445.208x136.458Y(19U24/

So it is sending:

Reset

Copies

DPI

Paper Type

Orientation

Define Top Margin at # Lines

Define Left Margin at Column #

PCL absolute positioning

Unsure

PCL Line termnialtion

It is not sending TRAY information, so why isn’t the printer using the tray that we have defined?

So now what can we do, the tray info is not being sent!.

The solution:

So, I tried hacking the paper type, but had no real control on how to ensure that the correct tray was being passed into the command string.  What I did determine what that the default # of copies was 1 – so this was not needed.

When I say hack the paper type, Change the A in &l26A to H and then try different paper types.  This did not work for a number of reasons.  Firstly the PDF’s were getting generated based upon the papersize and secondly you can’t print a letter based report to an A4 printer – JDE still embeds the printer paper type that the report was run against.

Therefore I hacked the jdekrnl.dll file (well a copy) to change the command for # of copies to tray selection.  I changed the X in &l1X to a H.  Therefore when you passed in a 1 for number of copies, this would use tray 1.  Pass in a 2 for copies, this will use tray 2 but only print 1 copy.  

So, when you choose my printer, which uses my custom “printer definition language” which uses the hacked jdekrnl2.dll file.  This swaps copies for output tray.

I used a hex editor to change the PCL commands embedded into the PCL file that is send to the printer.

So the code now interprets the count of prints to fill out what goes into the tray selection value.

Note that this is the server version of jdekrnl.dll.  I’ve called it jdekrnl2.dll and have located a master version of it in \\xxx\E811\DLL_HACK

So this is a safe mod, what are the net effects.

· Jdekrnl2.dll is ONLY used for printing with tray selection

· You cannot choose “number of copies” for the T2 printer, it must be 1 to choose the green paper.  It seems that the RICOH tray selection codes, which are not the same as HP standard ones.  102 is second tray etc, a little weird.

Therefore any job that goes to \\xxx\PROPSRIC7001T2 from JDE will print on green paper if you choose 1 copy.  Choosing 2 or 3 blocks the printer...  I think that you need to choose 102 or 202 based upon the following table:

If there is a tools release, this file should stay (the way new tools releases currently work).

Hex view of the code in the DLL, this is where the printf are defined for the PCL codes.  Note also that they bytes are little endian, so you need to be a little careful about which ones to change.

Address Book Data Privacy Enhancements

I’d forgotten about these, but they are pretty cool.  It gives you the ability to hide certain aspects of the Address Book Data based upon the each type.  You can obfuscate TaxID, Addresses, Phone Numbers, emails date of both and gender information for JDE roles based upon the AN8 type.

This was 811 functionality that I had forgotten all about.  So don’t go implementing row security when this is going to do the job for you.  Better than row security!

Setting up Address Book data security involves these steps:
1. Selecting the Activate Personal Data Security constant in the Address Book Constants (P0000).
Personal data security is inactive unless the Activate Personal Data Security constant is selected.
2. Setting up permission list definitions.
Use the Address Book Data Permissions program (P01138) to create one or more permission lists that specify which fields in the Address Book are secured.
3. Setting up permission list relationships.
Use the Permission List Relationships program (P95922) to determine the users or roles that are subject to each permission list.
Once you have set up Address Book data security, keep in mind that users can still view their own address book information, and secured fields are not protected under these circumstances:
a. Adding new Address Book records.
b. Running reports that contain the secured fields.
c. Viewing records in the Universal Table Browser (UTB).

There is some good doco on https://support.oracle.com search for P01138.

How to find the program, menu etc from this truncated screen shot

Ever been sent a screen shot of an error and you’ve had no way of determining where in the app this is from?  Well here are some basic and essential hints for tracking down the screen.

Remember that there are two elements of text above every form, firstly the menu identifier that launched the parent application and secondly the name of the form that the user is currently on.  From this you can generally track down where the user was.

How to work out the program from this information:

The components are:

Part of text	How to find details
“All WO”	Select TMOBNM from PS_PROD.PRODCTL.F9000 where TMLNGTASK = 'All WO' ;
“Work Order Activity Rules Search & Select”	Select SWFMNM from ps811.OL811.F9865 where SWMD = 'Work Order Activity Rules Search & Select' ;

Advanced user overrides (P98950), did you know?

I must admit that I was not 100% sure what occurred when UO’s were copied to PUBLIC, and what occurred with the existing individual overrides…  The following explains all.  I also did not know that public UO’s were promoted with the application – that is great!

When promoting an interactive application within Object Management Workbench (P98220), the *PUBLIC User Override records for the application will be automatically promoted when the interactive application is promoted. Therefore, it is not a requirement that *PUBLIC User Override records be added to a project in Object Management Workbench in order to be promoted.

NOTE: All users will now see the new user overrides unless they already have an override record for this Application/Version with their User ID/Role in the User Overrides table (F98950). If an individual User ID/Role override exists, it will be used rather than the *PUBLIC user override. When the system looks for a user override record for an application/version in the F98950 User Override table, it inquires in the following order 1)User ID, 2)Role, 3)*PUBLIC.
If a user using the *PUBLIC override, changes the tabs in anyway, a new user override record will be created for their specific User ID and the *PUBLIC record will not be changed. If no changes are made to the User Override information, the user will use the *PUBLIC user override record from the F98950 User Override table. No user specific user override records are automatically created if there are no changes made by the user.

Confusion with ODBC and 64 bit deployment server

The start of my confusion is how JDE is working.  I’ve changed all of the ODBC’s as I traditionally would for a new enterprise server.  This is exporting the reg file, changing the server name and importing the reg file – simple…

But, although the registry is updated, when I check the ODBC control panel – its not updated.  Also, JDE does not look at the correct machine.  So, although the registry is fine, ODBC is NOT and more importantly the ODBC that JDE uses is not fine.

Great article here on 32 and 64bit ODBC http://support.microsoft.com/kb/942976

Essentially;

• The 32-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\SysWoW64 folder.
• The 64-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\System32 folder.

So, we need to use the 32 bit version, best to create a shortcut to this on the desktop.

The 32 bit version

Sadly, it’s not the easiest thing to determine if a file is 64 ot 32 bit compiled / executable.  Very stupid.  As we are on the deployment server, we can use dumpbin, shipped with visual studio.  To use dumpbin from anywhere, start a cmd session and execute vcvars32 within that.

C:\Users\jde>"C:\Program Files (x86)\Microsoft Visual Studio 9.0\VC\bin\vcvars32.bat"

Then goto the file that you want to test and run dumpbin /headers

c:\windows\system32 dumpbin /headers odbcad32.exe |findstr magic

You could always look at task manager too:

Note that entering the command with the FULL PATH to the executable will give the correct results.  You cannot rely on the current dir!

C:\Windows\System32>dumpbin /headers c:\windows\system32\odbcad32.exe |findstr machine

            8664 machine (x64)

So it is just me, or weird that the odbcad32.exe application in windows\system32 is 64bit ?

Ok, so unequivocally we’ve been able to determine the architecture of the executable for the ODBC control panel program. 

I can tell you that JDE uses the 32 bit (SYSWOW64) version to load it’s system data sources.

So how can we bulk change these ones, because if you change the ones in the registry, HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI – they are not reflected in the C:\windows\syswow64\odbcad32.exe application.  So JDE does not pick up the changes.

Easy, just don’t double click the reg file, this will call the default (higher in the path) regedit.exe – which is the 64 bit version.  You need to open the c:\windows\syswow64\regedit.exe application and then choose the reg file that you want to import.

Phew – it’s that easy!

Simply:

• default apps are 64 bit (regedit.exe and odbcad32.exe) from the system32 dir
• JDE uses the 32 bit apps
• You need to manually run the 32 bit versions of these apps to see the correct data
• use dumpbin /headers to see the machine line for architecture info

Not blogged for a long time

I just thought I’d let you know that I’ll start mirror blogging, back to here and my work blog that is available at http://www.myriad-it.com/index.php?option=com_content&view=category&layout=blog&id=68&Itemid=106

I’ve been doing some exciting things with BSSV, BPEL and also using the new oracle templates, so I have a lot to blog about.

Watch this space!

 

Shannon