Wednesday, November 16, 2011

What's up with Vi

Continuing with my last few posts on basics of Unix world that a CNC needs from time to time, today I chose to talk about the Vi editor. Its one of the most powerful editors on the console and certainly must know aspect of CNC administration.

Here are the few commands i know and have used over the years in Vi extensively. If you don't get it straight away just practice with patience and you will be rolling in no time

If you do not want to go the full path straight away just concentrate on the "basicest"  operations of creating, navigating , inserting and deleting in a Vi editor and you will be good for a while!! :)


General Startup
To use vi: vi filename
To exit vi and save changes: ZZ   or  :wq
To exit vi without saving changes: :q!
To enter vi command mode: [esc]

Counts
        A number preceding any vi command tells vi to repeat
that command that many times.


Cursor Movement

h       move left (backspace)

j       move down

k       move up

l       move right (spacebar)

[return]   move to the beginning of the next line

$       last column on the current line

0       move cursor to the first column on the
current line

^       move cursor to first nonblank column on the
current line

w       move to the beginning of the next word or
punctuation mark

W       move past the next space

b       move to the beginning of the previous word
or punctuation mark

B       move to the beginning of the previous word,
ignores punctuation

        e       end of next word or punctuation mark

        E       end of next word, ignoring punctuation

        H       move cursor to the top of the screen

        M       move cursor to the middle of the screen

        L       move cursor to the bottom of the screen


Screen Movement

       G        move to the last line in the file

       xG       move to line x

       z+       move current line to top of screen

       z        move current line to the middle of screen

       z-       move current line to the bottom of screen

       ^F       move forward one screen

       ^B       move backward one line

       ^D       move forward one half screen

       ^U       move backward one half screen

       ^R       redraw screen
( does not work with VT100 type terminals )

       ^L       redraw screen
( does not work with Televideo terminals )


Inserting

       r        replace character under cursor with next
character typed

       R        keep replacing character until [esc] is hit

       i        insert before cursor

       a        append after cursor

       A        append at end of line

       O        open line above cursor and enter append mode


Deleting

x       delete character under cursor

dd      delete line under cursor

        dw      delete word under cursor

        db      delete word before cursor


Copying Code

        yy      (yank)'copies' line which may then be put by
the p(put) command. Precede with a count for
multiple lines.


Put Command
        brings back previous deletion or yank of lines,
words, or characters

        P       bring back before cursor

        p       bring back after cursor

Find Commands

?       finds a word going backwards

/       finds a word going forwards

        f       finds a character on the line under the
cursor going forward

        F       finds a character on the line under the
cursor going backwards

        t       find a character on the current line going
forward and stop one character before it

T       find a character on the current line going
backward and stop one character before it

; repeat last f, F, t, T


Miscellaneous Commands

. repeat last command

u undoes last command issued

U undoes all commands on one line

xp deletes first character and inserts after
second (swap)

J join current line with the next line

^G display current line number

% if at one parenthesis, will jump to its mate

mx mark current line with character x

'x find line marked with character x

NOTE: Marks are internal and not written to the file.


Line Editor Mode
Any commands form the line editor ex can be issued
upon entering line mode.

To enter: type ':'

To exit: press[return] or [esc]


MOVING

:# move to line #

:$ move to last line of file







Thursday, November 10, 2011

Lets talk .profile

The .profile file in a Unix environment is a "know all" file. It resides under the /home/<userid> dir of the os and it's the first one to be executed whenever that particular user logs in. This is the file that creates the environment for the user and has all the shell options and environment variables defined.

Before we go into how JDE uses this file and why is it important for it, lets see what all it has first:

First and foremost comes PATH:

As the name suggests this is where the path to the dirs of the installed software go. Lets suppose we have a Unix user account MaxPayne. If MaxPayne has all the paths to the software or commands he uses defined in the .profile , he can run those command from anywhere once he logs in to the Unix shell.

Then comes the Editor.

More often than not, when we talk about default shell its the BASH shell and MaxPayne is no different. His shell is bash too and his .profile has Vi as an editor. Vi Editor is one of the most powerful command line editors that we have. Refer my post for the details about using Vi. If you need to change your editor all you need to do is specify it here in the .profile. For the record MaxPayne loves Vi.

Now we see PS1

PS1 defines what the command prompt looks like in the shell. Typically you will find the server name at the command prompt. But MaxPayne is smarter, he has included the $PWD in the PS1 definition so that he can see which dir he is in without ever needing to run pswd again!!

Last common entry is the Alias

Aliases are used to rename some commonly used commands. For example alias dir=ls will help a confused windows user to get the directory information he needs. Aliasing dir with ls tell the unix system to run ls every time MaxPayne types dir.

All the above are default entries in the .profile of any user. When it comes to a JDE Enterprise server what we see are the Oracle homes and the oneworld homes defined in the .profile. When the platform pack is run in the system it creates a .oneworld file in the user dir. This file tells the jde processes where all the JDE files are. We load this .oneworld in the .profile of the JDE file owner. Unless this is done services will not be able to start. So now when you see load libraries failing or path related issues coming up on restart, you know where to check. Majority of the times a corrupted .profile is the source of this.

This in brief is what .profile means to the JDE CNC. If you need any help configuring yours feel free to drop me a comment.









Wednesday, November 9, 2011

Let’s Mount …..

When i was introduced t0 JDE the first and foremost thing that motivated me to go for becoming a CNC expert was that it gave me opportunity to work on UNIX.  Its beyond doubt one of the most formidable platforms for any business and i was a lover since my early college days :)

One of the biggest differentiating factors in Unix is its file system handling. People coming from the Windows world find it difficult to understand that the file storage has nothing to do with the  drives they see in the explorer but is actually a mounted space allocated from the memory available.  Its this “mounting” that makes any file system available for storage.

Syntax for Mount command is as follows:

$ mount /dev/hda3  /home

This command will “load” the resource or filesystem in disk drive hda3 into the local /home and then /home will have the memory on hda3 for use.

The best part of Mount is that you can not only mount hard drives connected to your server but use ones that are connected to the network as well.  This is what is meant when some one says “the db client is a NAS mount”!!

Remember that there is no registry to be maintained in Unix and we don’t necessarily have to install an application on a particular machine to use it. It’s all about the Environment Variable and the file locations.  So if you have an executable and the right parameters set for it in the environment variable, the executable knows how to take care of itself. It knows exactly where to look for the files it needs to execute.

Now when anyone says that the “db client is a NAS mount”, you can figure out that the DB client is installed on a different machine in the network and that particular drive is mounted in your Enterprise server and the Environment variables on your JDE Enterprise Server are set such that your  server knows where to look for the DB client files when its trying to connect to the DB. As far as both the servers concerned are in the same domain and can reach each other in quick time, this works seamlessly.

The biggest advantage that this brings is maintenance of the supporting Applications. If the company has several hundreds of databases this architecture helps the DB support group to maintain all the db’s on their own servers and all the app teams can just mount the locations. Patching and maintenance is spared of the fragmentation and the upgrades can be handled much more efficiently.

If you want to check all the mount points that are currently there on your server execute the following command:  more /etc/auto_direct

Have fun!!

Let me know if the post gives you a little bit of an insight on Mount as a UNIX feature. You can always check the man pages for details about any UNIX command. My intention over here was to drive away the initial fear of “what’s this!!” from anyone who is new to the world of Unix & CNC admin.

JDE CNC Interview Questions

If you landed up on this page searching for “JDE CNC interview questions”, you are at the right place. Read along to find what they ask, what they intend to ask, what they thought they were asking, what they cannot ask and how you should answer, what you should say in case you can’t answer or should you at all be interviewing for a CNC job and yes, you can thank me later.

The first thing that you should do is to stop searching for the interview questions and believe in the fact that there is no shortcut available to portray yourself as an excellent CNC. It’s not a set of instructions that you should know or a bunch of APIs you should know how to call and refer back to. It’s not the “do one time and forget it was ever coded” type of a job. You just cannot read a few pages and get into a CNC’s pinching shoes. No you can’t!!

CNC is a fire fight. It’s a trek through thorny bushes with a low quality slipper on in half pants on a dark night, may I add, without a torch. No, I am not over exaggerating a wee bit. Tell me if you can wake up in the wee hours of the night after you have had a three quarters of a large pizza and a couple of beer bottles to trouble shoot a failed package build or a hung UBE. Or get interrupted on a date for a question regarding why a UBE runs slow from citrix but fast from office. I don’t see any one nodding in affirmation. Yes, this is just a glimpse of a CNC’s work.

A CNC’s job requires a different attitude and a keen eye. It needs a person who loves technology to the extreme and is not bothered to hear complaints about it day in and day out. In fact, any complaints regarding a system to him, is an opportunity to sell an upgrade, a need to get more people involved. A CNC has to be a social butterfly and should be able to convince anybody and everybody that he is in control even in face of a catastrophe. He should know the system inside out rather than outside inwards.

So if you are asked to tell about package build, don’t act like it’s just a job that the dumb guy does at a developer’s command every now and then in your project. Rather be prepared with what’s an err file and how and when does the sts gets created. Know your F91300 when talking about schedulers and remember to talk about platform independence when trying to explain what’s so special about JDEdwards. If you are among the ones who cannot tell what’s the security type of a JDEdwards setup when the security server is not running, CNC is not for you. You just cannot handle the curve balls a CNC job throws at you if you have a unidirectional knowledge. Rather than looking for specific questions and answers invest your time in learning the system more. Learn to appreciate the technology within. Once you have enough respect for the system, you can go for any interview that a CNC job asks for. You will be ready for it.

Please remember that the views expressed are solely my own and I do not guarantee any success. But yes at least if you read this before posting for the quintessential “JDE CNC interview questions” in any JDEdwards forum, it will surely save you from some blushes.

If any CNC gurus land up here, please feel free to leave me a comment. And help me describe the job we all love (no I am not being sarcastic) with better examples than mine.

To all the budding CNC’s , good luck !!

Julian Date and its sql conversion..

Time and again we pull up UPMJs from the tables via backend and need to convert the date that gets pulled into Gregorian. Following query when appended to any date column in a jde table will give the Gregorian date for the corresponding Julian:

TO_CHAR(TO_DATE(XXXXXX+1900000,’YYYYDDD’),’MM-DD-YYYY’)

Notes:
- where XXXXXX is the column name to convert.
- this only works on 19th century onwards.


If you are more comfortable with XLS following are the two conversion formulae that will help:

=DATE(IF(0+(LEFT(A1,2))<30,2000,1900)+MID(A1,2,2),1,RIGHT(A1,3)) where A1 is your date in Julian

="1"&RIGHT(YEAR(A1),2)&TEXT(A1-DATE(YEAR(A1),1,0),"000") where A1 is your date in gregorian.

Firefox Sync… a real make-life-easy feature..

Ohh yess.. this feature of Firefox deserves a real good shout out and is in every way remarkable for people who keep changing PC.. read.. keep getting forced to change PC’s. Its the next level to exporting-importing bookmarks … its Syncing your entire Internet ecosystem with your hand held device such that you can sync it with any other device you need to use Firefox on.

If you use Firefox 3.6.* you need to get the “Firefox Sync” Add-on installed to begin using this feature or if you use Firefox 4 beta its there by default. Sync not only moves the book marks between systems but also the stored passwords as well as the cookies. When you install the add-on it asks you to register and then enter a pass phrase for encrypting your data. Once done, all your bookmarks along with the passwords and the cookies are transferred to the Firefox server in an encrypted format. The beauty of the system is that even if you forget the pass phrase used, it stores the encrypted key on your system and it can be used anytime you are trying to sync another device.


The next step is to install the iPhone app Firefox Home and sign in with the user id created for Sync. Once through, all the data is then available on your phone and ready to be synced with any other PC you need to use Firefox on.

There is currently no app for the Android platform because Mozilla is working on Fennec, the complete firefox explorer for android. So there is Hope

Sp24 Special Dep Server Config..

In line with my last post on SP upgrade on Unix machines, here’s a bit on the special configuration that comes with SP24. Its only needed if you have Vista or Win7 client machines. A few changes have been made to the files being placed under the C: and C:\Windows directories. Since both vista and win 7 restrict programs from accessing these DIR hence JDE has developed symbolic links. Now these Symbolic links are created by the new E1InstallService which needs to be configured on the Dep Server. After building the full package post-SP-upgrade, just before you are about to install a Vista 0r a Win 7 client follow the following steps to enable this service:

1. In the Install.inf on the Dep Server add the following line: OracleE1InstallService={DEP_SERVER_PATH}\E1ServiceInstallation,Setup.exe,/s

2. Create a OracleE1InstallService_silent_setup.inf under E1InstallService folder with the following lines

[SILENT SETUP]
WorkstationDirPath = [your path under c:\Programfiles]
InstallPath=c:\
LaunchPath= {DEP_SERVER_PATH}\OWCinstall\setup.exe
AutoStart = 1

3. Add your user groups under the [E1GlobalGroup] section of the install.inf. Not doing so will result in a dialog box being thrown to alleviate user privs when the user tries to log in a win7 fat client.

4. Now you are all set to install a fat client on a vista or a win 7 client with sp 24.


Foot Note: To check the current version of the Service pack running on your system, go to System\bin32 dir on the deployment server find jdekrnl.dll , right click on it , go to properties and check File Version under the Properties tab.

DataSource Master and WSJ …

One of the most used app in JDE is WSJ or Work with Submitted Jobs or P986116. Irrespective of our roles in JDE we use this app to check for the job statuses that run on the servers. This is one of the trickiest things to configure when you have a split system and are going through a migration which involves changes in the hostnames of the parent servers. Lets have a sneek peek of what goes on behind the scene when WSJ is executed from a client pc.

1. P986116 gets executed and it lists the Data Sources from F98611 (Data Source Master).

2. When you hit on Find on P986116 the app queries the System.F98611 for all the Server type (SVR) data sources which are then returned on the grid.

3. Now once the data source is selected, the system sets the OMSRVR from the F98611 record as the hostname to be serached in SVM.F986110.

4. Status of the jobs are written in the SVM Job Control Status Master (F986110).

5. System then queries SVM.F986110 pulling up records for which the hostname matches the SVR of the DataSource picked.

If a Unix migration is being attempted care must be taken that the hostname of the machine matches the exact case of the OMSRVR field in F98611. In case of systems with muliple Enterprise servers the SYS.F98611 becomes the spine of everything working together. If there are multiple SYS’s then all of them need to be updated such that a user can query status of his job across the environments. Unless the SYS.F98611 are properly updated the DataSource changes will not show up and users will not be able to check job statuses of say, a job running in PD while he is logged in to QA.


JDE Service Pack upgrade for Unix Systems

One of the most common type of projects in JDE is SP upgrade of an instance. Deployment Servers being windows machines are the easiest ones to Upgrade. And if you are on XE or Erp8.0 You just need to get the latest ESUs from the Change assistant and get started. For SO upgrade backup the OneWorldClientInstall, System and SystemComp folders , execute the Setup.exe of the SP , check the ptf.log and voila your SP upgrade of the Dep Server is done.

The real test of skill lies in the upgrade of a unix ent server and this is what i will enlist step by step in this post.Lets start from the download.

1. Download the correct SP file from Update Center. I recommend using the latest Change Assistant to get these. Select Type-> EnterpriseOne Tools Release, Release -> either 8.9x or Xe as the case may be with you , Platform -> SUN (based upon the Ent server OS you have) and if you know the version same of the SP you are searching for , you can shorten the result by typing a wild card in the Name filed.

2. In the list of SP’s shown select the latest version that it shows. As a rule of thumb I typically wait for *.1.1 and higher versions to appear. For instance if i am about to do an SP24 upgrade I will today be downloading SP24.1.3. If you are adventurous please feel free to download *.0.0 (specially recommended for POC’s , believe me they give a stupendous learning experience).

3. After you have saved your downloaded Par file for the upgrade. Open it with Winzip oor Winrar or any other unzipper and extract the files.

4. The file contains a system.Z file for the ENT server. Now the next step is to send it over to the ENT server. There are multiple ways to achieve this. If you site allows WinSCP install. Just install it connect to your ENT server, create a temp file on the server and drag and drop this system.z from the local machine onto the dir you just created on the ENT server. To avoid unseen data loss try and configure winscp to transmit in binary mode. Now if winscp isnt possible and you dont have hummingbird as well just remember that everysite allows an FTP connection between the deployment server to the ent server. Make sure that ftp services are enabled on the DEP server IIS and sent it to the ENT server using ftp.

5. Now once the file has been sent over to the ENT server login to th eent server using telnet or PUTTY as might be the case for you using the same user id that runs the JDE services on the ENT server. Go into the temp dir that was created in the step above and execute a “chmod 777 system.Z” command.

6. Stop the JDE services.

7. Go to your /dir/jdedwardsoneworld/ver### dir and execute ” mv system system_bak_date” command. This will create a backup of the system dir. Now you are ready to replacs this old system with the new system.

8. Go to the temp dir that has system.Z and execute the command “zcat system.Z | (cd /dir/jdedwardsoneworld/ver###;tar xvf – )”. This will unzip the system.Z and create a system dir for you in the /dir/jdedwardsoneworld/ver### .

9. Go to /dir/jdedwardsoneworld/ver###/system and rename the RunOneWorld.sh and the EndOneWorld.sh by the following command cp RunOneWorld.sh RunOneWorld.sh_original_sp and cp EndOneWorld.sh EndOneWorld.sh_original_sp. This will preserve the original scripts that came with the SP.

10. Go to /dir/jdedwardsoneworld/ver###/system and copy the RunOneWorld.sh and the EndOneWorld.sh from the system_backup_date into this system dir.

11. Execute a chmod 555 for system/lib/* system/libv32/* and Run cat ptf.log, it should show the latest SP name.

12. Now its the time to give up all hope in life, give up on your weekend plans and question your decision of becoming a CNC and after you have done all that execute ./RunOneWorld.sh

13. If you see the One World Startup Complete message do a Joy Dance and execute a portest. If porttest comes out successful, you are ready for a full package build and take it from there.
The above 12 steps will only help if you are not changing the DB. Remember that if the SP upgrade also involves a DB upgrade you will need to do a variety of changes to the JDE.ini , the .profile etc for the change to take effect.

14. A lil prayer helps!

Let me know if this helps any one of you! Also please feel free to point out any points that I might have missed. Happy SP Upgrading

Inclusive or Exclusive Row Security.. And the fight goes on!!

This is more like the Parry’s Coffee Bite Ad that we used to watch when we were kids… its toffee…its coffee. The only difference being rather than the candy that we enjoyed Row Security sometimes comes up as a nightmare.

The way ahead is much easier than what you may think of. If you are on 8.9 or higher release just go to the Form exit for Exclusive\Inclusive in the Security Workbench application (P00950) and you can see with your own two eyes what type of security is maintained in your system

If you are on my beloved ERP 8 just do it the old school way by running this query on your F00950:

Select * from sys7334.F00950 where FSUSER = ‘EXCLUSIVE’;

If a value turns up for the query it means you have inclusive security enabled in the system.

In case you are wondering about the meaning of both the security types and do not have access to GSC here are my two cents worth of idea:

Inclusive row security simply means that the value that the user can see are included in the from and thru values defined for him or his role.

Exclusive row security means that the values mentioned are excluded from the user’s view.

The select queries are generated based upon this security setup for the user by the jde middleware. Now I know what’s the real use of the middleware

Convert JULIAN to standard date using SQL….

EnterpriseOne stores dates in Julian using the following format CYYDDD (“C” is the century, “YY” is the year and “DDD” is the number of days since the start of the year).
To convert a julian date to a standard (gregorian) date format:

TO_CHAR(TO_DATE(XXXXXX+1900000,’YYYYDDD’),’MM-DD-YYYY’)

Notes:
- where XXXXXX is the column name to convert.
- this only works on 19th century onwards.

if you need help putting it in a query lemme know…

Obtaining a list of tables for a specific column in Oracle DB.

If you are a CNC you definitely know that there may be multiple ways to do the same thing because that’s imperative for success. Now if I have a column name and I need to know all the tables in the database which have the particular column, I can easily do a XREF in fastpath and search for all the tables that use a specific data item. But in case you haven’t build the cross reference and there is a need to find it out , here’s what can be done in the old school way:

DBA_TAB_COLUMNS is a view that will help us obtain a list of tables for a specific column in Oracle database
.
example:

SELECT owner, table_name, column_name
FROM DBA_TAB_COLUMNS
WHERE column_name like ‘%ADDJ’
ORDER BY owner, table_name

The ID used to login to the db must have dba privilege for the query to work….. after all  what’s life without any limitations

SQL query to find Schema Sizes and space info….

SELECT T.TABLESPACE_NAME “TABLESPACE NAME”, 
TO_CHAR(T.TOTALMB/1048576,’99,999,999.99′) “TOTALMB”, 
TO_CHAR(F.FREEMB/1048575,’99,999,999.99′) “FREEMB”,  TO_CHAR((T.TOTALMB-F.FREEMB)/1048576,’99,999,999.99′) “USEDMB”,  TO_CHAR((F.FREEMB/T.TOTALMB)*100,’999.9′) “PCTFREE”,  TO_CHAR(T.MAXMB/1048576,’99,999,999.99′) “MAXMB”, OBJECTS “TOTAL_OBJECTS” FROM (SELECT TABLESPACE_NAME,SUM(BYTES) TOTALMB, SUM(MAXBYTES) MAXMB FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) T, (SELECT TABLESPACE_NAME,SUM(BYTES) FREEMB FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F,  (SELECT TABLESPACE_NAME,COUNT(*) OBJECTS FROM DBA_SEGMENTS GROUP BY TABLESPACE_NAME) S WHERE T.TABLESPACE_NAME=F.TABLESPACE_NAME(+) AND T.TABLESPACE_NAME=S.TABLESPACE_NAME(+);

Using the netwm utility on UNIX Application Servers

The netwm utility can be found under the $SYSTEM/bin32 directory of a JDE unix application server. This utility can be used to get/clear the net and kernel procedures running for that instance.

Following is the syntax for the utility:

usage: netwm [-r] [-c pid]

where:
-r optionally clears the kernel request counts.
-c optionally clears the process information for pid which can be either a net or kernel process.

If only the structure header information is returned by the netwm command it means that the services are down and need to be restarted.

My two cents of R92TAM and R98CRTGL!!

What R98CRTGL does, is to go through F9860 and find all tables that are neither bootstrap nor central objects and call the JDB_OpenTable API for that table.The JDB then finds the table specs.

It looks for the specs in glbltbl.xdb and glbltbl.ddb. As normal, the specs wont be there as we are trying to build them and it then looks into local TAM.If it doesnot find the dataitem in local TAM or dddict
it will then go to the RDB specs and JITI them. It is to avoid this JITI that we need to run R92TAM prior to running R98CRTGL.

SQL to find Out all the active users in the system

select distinct SCUSER NT_ID, ABALPH, ULUGRP
  from sys7334.F0092 a, sys7334.f98owsec b, proddta.F0101 c
 where a.ULUSER = b.SCUSER
   and a.ULUSER = c.ABALKY
   and b.SCEUSER = '01'
   and c.ABAT1 = 'E'
   order by ULUGRP;

Check Checkin/out status of Objects

Objects Checked out
select sipathcd as “Path Code”, simkey as “Machine”, SIOBNM, siuser as “User ID” from OBJ7334.F9861 where SISTCE != ’1′ order by siuser, siobnm;
Versions Checked out
select VRENHV, VRUSER as “User”, VRPID as “Object”, VRVERS as “Version”, VRMKEY as “Machine” from DV7334.F983051 where VRCHKOUTSTS=’Y’ order by vruser, vrpid, vrvers;
Objects created but never Checked-in
Select POOMWUSER, POOMWOBJID, POOMWMKLEY, POPATHCD from SYS7334.F98222 where POOMWOT !=’UBEVER’ and POOMWOST=01 and POOMWCHS=’1′ order by POOMWUSER, POOMWOBJID, POPATHCD;
Versions created but never Checked-in
Select POOMWUSER, POOMWOBJID, POOMWMKEY, POPATHCD from SYS7334.F98222 where POOMWOST=01 and POOMWCHS=’1′ and POOMWOT=’UBEVER’ order by POOMWUSER, POOMWOBJID, POPATHCD;

The Quintessential Fake Check-out technique

Won’t I be doomed if I ever forget this technique of faking a check out for the developer. In a scenario where a object has been modified without checking it out and
there is no save location available we can perform a fake checkout by following the simple steps below:
1. Open the Object with its Designer Tool in OMW
2. Check-Out the object in OMW.
3. Save the object in its Designer Tool.
…. light flash!! fake check-out is done.

SQL to find Out all the active users in the system

Execute the following SQL command to get all the active users with a particular role (ABCD)
select distinct SCUSER NT_ID, ABALPH UserName
from sys7334.F0092 a, sys7334.f98owsec b, devdta.F0101 c
where a.ULUSER = b.SCUSER
and a.ULUSER = c.ABALKY
and a.ULUGRP like ‘%ABCD%’
and b.SCEUSER = ’01′
and c.ABAT1 = ‘E’
and b.SCUSER not in
(‘JDEXYZ’, ‘JDE’, ‘AFPCRPMON’);

Search for an application when you know what it does…

For newcomers into JDE CNC there are times when we forget the P***** name of an application that we want to use. For instance you are creating a new user in the system and now want to add the Role to the ID. Alas!! newsflash, you just can not remember the name of the application or the menu link to execute it. What a hopeless situation, which warrants asking for some help.  Well rather than picking the phone up and calling here’s what else can be done:
  1. Go to fastpath and type IV [Interactive Version]
  2. In the screen that appears open up the Visual Assist
  3. In the QBE for Description type %Role%  and hit find
  4. There you see the bright light flash. P95921 comes right in the list :)
As you search for an application in IV you can similarly search for a UBE in BV or a Table in the UTB visual assist.

Things to remember will be that search behaves differently for different databases in JDE. For Instance if you have a MS SQL DB and you search for say a user name in P01012 , it will be case-sensitive when you search in the Alpha Name field but the case wont matter if you have an Oracle DB.  :) I tried to clear one confusion but created another !!! Well that’s life… and the challenge of the unknown….

Windows Tricks

Contrary to the popular belief that E812 is the biggest user base of Oracle and no one uses XE or ERP8 any more, a lot of us work with clients who have  considerable investments in the good old versions of XE and ERP 8. Working with these versions means a CNC is constantly challenged by maintenance of Fat clients.
Imagine your self working to set up a new user and finding out that the admin access is missing and you need to add the user up. But the local policy denies you access to Computer management . Typical CNC day right :) Here is what you can do:
  • net local group Administrators domain\username /add
The user will get added to the local admin group and you can finish ur work and leave :)
Another important command with net is “net use”. It helps you to map the deployment server share to the user’s pc with the help of any id that you know has access to the share
  • net use * //depservername\ver /user: domainname\userid /persistent: no

SQL that gives all the details of all the tables…

SELECT TDOBNM, –Table Name
(Select SIMD From OBJ7334.F9860 Where SIOBNM = TDOBNM AND SIFUNO = ‘TBLE’)
Table_Description, –TableDescription
TDSQLC, –Column Name
(Select FRDSCR From DD7334.F9202 Where FRDTAI = TDOBND AND FRSYR = ‘ ‘)
Column_Description, –Column Description
(Select DRDL01 From DD7334.F9210, F0005 Where FRDTAI = TDOBND AND LTRIM(RTRIM(FROWTP)) = LTRIM(RTRIM(DRKY)) AND DRSY = ‘H98′ AND DRRT = ‘DT’)
Column_Type, — Column Type (JDE)
(Select FRDTAS From DD7334.F9210 Where FRDTAI = TDOBND)
Column_Length, — Column Length (JDE)
(Select FROWDI From DD7334.F9210 Where FRDTAI = TDOBND)
DD_Item, — Data Dictionary Item
(Select FROWER From DD7334.F9210 Where FRDTAI = TDOBND)
Edit_Rule, — Edit (Validation) Rule
(Select FROER1 From DD7334.F9210 Where FRDTAI = TDOBND)
Edit_Rule_Parm1, — Edit Rule Parameter 1
(Select FROER2 From DD7334.F9210 Where FRDTAI = TDOBND)
Edit_Rule_Parm2, — Edit Rule Parameter 2
TDPSEQ — Column Number
FROM DV7334.F98711 WHERE TDOBNM Like ‘F09%’ ORDER BY TDOBNM, TDPSEQ

Scheduler Server Jobs Status Update Issue with Oracle Database

The scheduler kernel uses the *Scheduler record in F91300 to know what machine is running the Scheduler Service. With the machine name, then finds in F98611 the Logical Datasource for that machine
and uses the value in the Database Name as the Server Map datasource for the bootstrap tables.

When the database is other than Oracle (hence ODBC), the Database Name field value coincides with the naming convention of Datasources for OneWorld/E1 (i.e ENTSERVER – B7334 Server Map).
But if the database is Oracle, the Database Name is the ORACLE DB name (i.e. OWERP8, which does not exist as a ‘Server Map’ datasource in F986110 for the Server).

Manually create a logical datasource that is a copy of the Server Map datasource by following the instructions below:

1. Sign into EnterpriseOne.

2. Run the “Database Data Sources” application (P986115).

3. Select the “ServerName – B7333 Server Map” from the “Machine Search & Select” form.

4. Make sure that the value of the “Data Source Use” search field of the “Work With Data Sources” form is set to “DB”. Then, click on the Find button.

5. Search for “ServerName – B7333 Server Map” data source in the grid. When you have found the data source in the grid, hightlight it and click on the Copy button of the “Work With Data Sources” form.

6. For the name the new database datasource, use the name of the Oracle database that appears in the JDB9900245 error message. Do not change any other fields on the “Data Source Revisions” form.

7. Click on the OK button of the “Data Source Revisions” form to save the new database datasource.

8. Exit out of OneWorld.

9. Restart the EnterpriseOne services for the change to take effect.

Corrupt Scheduler Server Record..

When JDE scheduler hangs up for certain reasons and none of the schedule jobs look like they will be executed, its very tempting to restart the scheduler server from the P91300 application. But remember in JDE there is a concept which states that just because an option is there it doesn’t necessarily mean that it will work (Product Packaging anybody!!!).
If you try to stop and start the scheduler server from the P91300 application, it corrupts the *Scheduler record in the system.f91300 table.
To start the scheduler server again stop the jde services, update the SJSCHUSER column with the security server port number for the setup and restart JDE services again.