if you are debugging some code, from a CNC perspective–development notes

Ever been knees deep (I said knees) in code and wondering why a BC is being read and a GC etc etc

Here is a handy refernce from oracle for what they are and when they are populated

Note that you might need to also view some of these flows to know exactly when a GB is written to a GC…  https://docs.oracle.com/cd/E17984_01/doc.898/e14706/grid_controls.htm#g8d6ab57f7cedeaac_ef90c_10a77c8e3f7__7257 

 

Available Object Code	Description
BC	A column in the business view (BV). BCs for both the form view and the grid view appear in this list. The system fills these columns with values from the database when it performs a fetch. The system writes these values to the database during an add or update.
GC	A column in the grid. The row that the value references depends on which event is accessing the GC. During the fetch cycle, it is usually the selected row. In some circumstances, CG objects also denote a particular physical column in the grid instead of a value. An example is the Set Grid Font system function.
GB	The grid buffer. This buffer is one row of data that is independent of the lines that the system reads from the database and writes to the grid. The GB enables you to manipulate column data for a line that you want to insert or update without affecting the present state of the grid. You access the GB through an available GB object, which appears after the GC objects in the list of available objects in Event Rules Design. Each grid contains only one instance of each GB column.
FC	A control on the form. If the control is a database item, this field corresponds to a BC object. Furthermore, if the control is not a filter, the FC object represents the same value as the BC object, and changing one of these results in changing both.
FI	A value passed through a form interconnection. You access this object either to read values that are passed into the form or to set values to be passed back. These objects correspond to the elements of the form data structure.
PO	A value passed from a PO. These values are passed into the application when a user launches it. Any form in that application can access them. POs can either be entered by the user, or they can be set up in a particular version of an application.
QC	A cell from the QBE line in the grid. These objects represent the values in any QBE cell on the grid. They include wild cards, but do not include any comparison operators. Likewise, assignments to these objects can include wild cards, but not comparison operators. To set comparisons, you must use a system function.
HC	A hypercontrol item. A hypercontrol item is a menu item or a tool bar item.
VA	ER variables. These objects represent any variables that you set up in ER.
SV	System variables. These objects represent some environment variables that are accessible to ER.
SL	System literals. These objects represent some constant system values that are accessible to ER.
TP	Tab page object.
TK	A column in the table that contains the table ER.
CO	A constant, such as the return code for an error.
TV	Text variables.
RC	Report constants for a batch application.
RV	Report variables (batch application).
IC	An input column (table conversion).
OC	An output column (table conversion).

change a table and save off the data–how to guide- oracle syntax

A classic situation when you create a table for a development requirement and then you need to add a couple of columns, but don’t want to lose your data.  There is a couple of ways of getting this done.  I’d do “ALTER TABLE ADD column” operations.  This is super simple and I’ll outline the procedure with pseudo code.

First drop constraints and indexes on the table.

select 'ALTER TABLE ‘ || owner || ‘.’ || table_name || ‘ DROP CONSTRAINT ' || constraint_name ||';' from all_constraints where table_name = 'F55MYRXX' and owner = 'TESTDTA';

alter table testdta.F55myrxx drop constraint F55MYRXX_PK;

select 'DROP INDEX ' || owner || '.' || index_name || ';' from all_indexes where table_name = 'F55MYRXX' and owner = 'TESTDTA';

DROP INDEX TESTDTA.F55MYRXX_0;

alter table testdta.F55MYRXX rename to F55MYRXXBAK ;

--so now you have your table as a BAK – cool – this is ready to insert the data from (or use it to alter and then generate the indexes again).

Note that you need to do the above, as the rename does not rename the constraints or the PK’s that they will give you problems when you go to generate the table from E1.

Generate the new table structure from OMW

Now, see the column differences:

select column_name from all_tab_columns where table_name ='F55MYRXX' and owner = 'TESTDTA'
minus
select column_name from all_tab_columns where table_name ='F55MYRXXBAK' and owner = 'TESTDTA';

COLUMN_NAME                   
------------------------------
XXIDNUM1                      
XXIDNUM2                      
XXIDNUM3                      
XXIDNUM4 

Note that if you get nothing, you’ve done SOMETHING WRONG.  Delete your global tables and dddict and ddtext files on your fatty and try again.  Perhaps get the specs of the table.  You should get column differences.

So, you can use this information as you’d like.  Either copy back in your data, or if you have heaps, run some alter table statements.  You need to know the oracle data types if you are going to execute alter table, get these with the statement below:

select * from all_Tab_columns where owner = 'TESTDTA' and table_name = 'F55MYRXX' and column_name in (
select column_name from all_tab_columns where table_name ='F55MYRXX' and owner = 'TESTDTA'
minus
select column_name from all_tab_columns where table_name ='F55MYRXXBAK' and owner = 'TESTDTA');

Then alter your backup table, or use the alter statements for the future environments.

select 'ALTER TABLE ' || owner || '.' || TABLE_NAME || 'BAK ADD (' || COLUMN_NAME || ' ' || DATA_TYPE || '(' || data_length || ') );'  from all_Tab_columns where owner = 'TESTDTA' and table_name = 'F55MYRXX' and column_name in (
select column_name from all_tab_columns where table_name ='F55MYRXX' and owner = 'TESTDTA'
minus
select column_name from all_tab_columns where table_name ='F55MYRXXBAK' and owner = 'TESTDTA');

run the output:

ALTER TABLE TESTDTA.F55MYRXXBAK ADD (XXIDNUM1 NCHAR(40) );
ALTER TABLE TESTDTA.F55MYRXXBAK ADD (XXIDNUM2 NCHAR(40) );
ALTER TABLE TESTDTA.F55MYRXXBAK ADD (XXIDNUM3 NCHAR(40) );
ALTER TABLE TESTDTA.F55MYRXXBAK ADD (XXIDNUM4 NCHAR(40) );

Then your backup table has all of the columns.  Note that you might need to do some cool things with 1 char strings and the like, but you get the picture.  The cool thing about this method is that you can now insert you data back with a very simple command [as you’ve added the columns to the backup]:

insert into TESTDTA.F55MYRXX select * from TESTDTA.F55MYRXXBAK ;
commit;

Once you have the above, you could use it for all your subsequent environments without all of the drop malarky, just change the owner name.

The other way to do this is to do an insert into select * from and list all of the columns

insert into TESTDTA.F55MYRXX (COL1, COL2…) select COL1, COL2…,0,’’,’’,0 from TESTDTA.MYRXXBAK;

Note that the ‘’,0,’’,0 are for the new columns at the end of the table.

The real performance impact of logging and nettrace to JD Edwards

Ever wanted to know what the affect of enabling logging was going to be on your back end users?  Have you ever really wanted to get some sqlnet tracing and some jdedwards logging on a problem – but not been brave enough because of the impact on your servers and your end users.  Well, I might have some handy metrics for you.

I’ve been working with a client that gets a little too many of these bad boys when they are under load:

D:\JDEdwards\E910\log>findstr  ORA-03113 *
jdedebug_1880.log:Jan 19 19:42:12.516059 - 1880/7520 WRK:DGTEST01_09F8D008_P01012       OCI0000179 - Error - ORA-03113: end-of-file on communication channel
jdedebug_5204.log:Jan 19 19:20:18.520003 - 5204/4840 WRK:DGTEST01_0B3BFEF0_P4310        OCI0000179 - Error - ORA-03113: end-of-file on communication channel
jdedebug_6800.log:Jan 19 19:43:08.980002 - 6800/2952 WRK:DGTEST01_0975AA28_P01012       OCI0000179 - Error - ORA-03113: end-of-file on communication channel
jde_1880.log:   OCI0000179 - Error - ORA-03113: end-of-file on communication channel
jde_5204.log:   OCI0000179 - Error - ORA-03113: end-of-file on communication channel
jde_6800.log:   OCI0000179 - Error - ORA-03113: end-of-file on communication channel

Yep, this is not good.  It’s essentially the client saying my connection has been terminated, I give up.  There is generally a requisite alert in the database error logs.

Tue Jan 12 21:21:49 2016

Errors in file /u01/app/oracle/diag/rdbms/jdegsuat/JDEGSUAT1/trace/JDEGSUAT1_ora_90089.trc  (incident=120946):

ORA-03137: TTC protocol internal error : [12333] [7] [2] [0] [] [] [] []

Incident details in: /u01/app/oracle/diag/rdbms/jdegsuat/JDEGSUAT1/incident/incdir_120946/JDEGSUAT1_ora_90089_i120946.trc

Of course, oracle support are not going to touch this without a shed load of logs – so guess what – I’m going to give them a shed load of logs.

sqlnet.ora has been modified with the following (this is a 12C database and client).

trace_level_client = 16

trace_file_client = cli

trace_directory_client = d:\trace

trace_unique_client = on

trace_timestamp_client = on

trace_filelen_client = 100

trace_fileno_client = 2

log_file_client = cli

log_directory_client = d:\trace\log

tnsping.trace_directory = d:\trace\trace

tnsping.trace_level = admin

Not too sure how many of these are working, because all of the logs are being written to d:\oracle\diag – not the dir that I’m specifying…  Anyways…...

So my scripts with a mixed work load saw up to 7.1% net speed loss with logging (interactive speed, averaged over 1000 sessions executing the same mixed workload).  An average of 14.6% slower with sqlnet tracing and jde logging. 

I’d expect batch to slowdown more, but this was pretty good.

Note that the server was running at about 85% CPU utilisation when logging was enabled, not the 12% that it was without logging enabled!!!

reverse engineer forms for mobile development

Okay, things are going to get fairly technical here, but that’s what this blog is all about.

We are writing a lot of mobile applications for JD Edwards at the moment.  We have our apps integrating with GPS, doing google map overlays (wait – post coming) and more.

In this journey we are looking for better ways to write the applications, better ways of determining all of the control IDs, so that when we import these into the mobile development – they can be flexible and dynamic.  i.e. we want to kind of create a UO of a form which only exposes the cut down version that we want on our mobile device (that type of thing).  This is like creating a runtime environment which will read an XML document, and based upon this (at runtime) display the various JDE screens and functionality.  Now, this is the end game – but there are some cool intermediate steps we can go through.

Firstly, we have a massive grid and want to be able to choose any field on the grid for QBE.  Then I want to also see the DD information for the item, to determine the default lookup screen.  This default lookup screen and default values will be great for our custom app to do things at runtime.

The problem is that you cannot get the ID’s of all the internal items from the HTML or scripts in JDE.  You can’t view source and see all of this additioinal information.  You cannot view the BLOB in F98751 to see what the code is doing (because of byte order which is the same in the F983051)…  You cannot print out the values from FDA either, which is a pain.

But, you can save your application as a par file, rename it to a zip file and then start looking around:

Above shows from OMW, hit save and choose zip file.

And then looking into specs.zip

Then in the fdaspec folder:

So for every object on the form, you have a nice, neat separate XML document.  extract these to a directory:

Open one of the controls that you know is in the grid, for example:

<?xml version="1.0" encoding="UTF-8"?>
<FDA ApplicationName="P55WOSF" xmlns:et="http://peoplesoft.com/e1/metadata/v1.0/erptypes" xmlns="http://peoplesoft.com/e1/metadata/v1.0">

<FDARecord>

<FDAColumn FormName="W55WOSFA" GridID="1" SequenceNumber="34" ObjectID="50" ColumnTitleId="2036" NumberOfTextChar="15" Visible="true" InputCapable="true" SortOrder="A" Flags="14336">

<et:Dbref szTable="F1201" szDict="DADS"/>

<et:DdOverrides/>

</FDAColumn>

</FDARecord>

</FDA>

The great thing about the above is it maps the ObjectID to the Table and DD item, therefore we can do a lookup on the F9210 for the DD defaults to get Alpha Text and also the default lokup information.  Nice.

We’ve built a custom table that indexes all of this information, we read this JD Edwards table at runtime to dynamically display the text that we want to display and also proper lookup values when doing data selection.

I think I can, I know I can

This is the first blog of 2016, which I hope to make the year of the blog.  Thanks so much to the people of open live writer for making this possible again.  http://openlivewriter.org/ I cannot coomit until this posts.

I need to add my source code plugins again, but this is small price to pay to be able to blog again.