X

This blog introduces how to diagnose Agile PLM related issues.

Recent Posts

Weblogic URL Redirect to Context Path

To visit web application deployed in Weblogic, we usually access it through its context path like http://server/myapp . But many people expect the URL to be friendly and short enough as http://server only. Here is a very simple solution, deploy a separate simple web application with root context-root to achieve. Note: this is not related to term of Proxy or Load Balancer. A simple web application (say DummyApp) demonstrates the solution with following weblogic.xml To access this DummyApp, we have to input URL http://server/DummyApp . W can context-root as below to make it accessible through http://server only. That is to say, if access http://server , browser will redirect the request to http://server/DummyApp because we define it as the default application. <?xml version="1.0" encoding="UTF-8"?><weblogic-web-app xmlns="http://www.bea.com/ns/weblogic/weblogic-web-app"><context-root>/</context-root></weblogic-web-app> web.xml A very simple web.xml to have a default welcome page index.html. <?xml version="1.0" encoding="UTF-8"?><web-app xmlns="http://java.sun.com/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" version="2.5"><display-name>dummyWeb</display-name><welcome-file-list><welcome-file>index.html</welcome-file></welcome-file-list></web-app> index.html A simple static HTML page to redirect DummyApp to http://server/myapp , using HTML meta with the http-equiv parameter set to "refresh". <html><head><meta http-equiv="refresh" content="0;url=/myapp"></head></html> How it works When we access http://server, Weblogic will redirect our request to its default application DummyApp's index.html, that is http://server/DummyApp/index.html . And index.html immediately routes to http://server/myapp as we expect.

To visit web application deployed in Weblogic, we usually access it through its context path like http://server/myapp . But many people expect the URL to be friendly and short enough as http://server...

Troubleshooting com.agile.ui.web.security.WebSecurityException

ESAPI is an open source used by Oracle Agile Web Client to protect Web application's security, avoid kinds of web attack like XSS, Injection, CSRF and so on. This API component must be present and your HTTP request must legal, or Agile WebClient throws com.agile.ui.web.security.WebSecurityException error and refuse to service client's request. I will discuss briefly how to handle two kinds of issue of WebSecurityException.Case of WebClient UnaccessibleThis only happens if below three ESAPI configuration files are lost, not readable or not of Agile Customization.antisamy-esapi.xmlESAPI.propertiesvalidation.propertiesWe will get error message in browser with following.Error 500--Internal Server ErrorFrom RFC 2068 Hypertext Transfer Protocol -- HTTP/1.1:10.5.1 500 Internal Server ErrorThe server encountered an unexpected condition which prevented it from fulfilling the request.And error in server log as following.Attempting to load ESAPI.properties via file I/O.Attempting to load ESAPI.properties as resource file via file I/O.Not found in 'org.owasp.esapi.resources' directory or file not readable: /u01/agile/agile933/agileDomain/ESAPI.propertiesNot found in SystemResource Directory/resourceDirectory: .esapi/ESAPI.propertiesNot found in 'user.home' (/home/oracle) directory: /home/oracle/esapi/ESAPI.propertiesLoading ESAPI.properties via file I/O failed. Exception was: java.io.FileNotFoundExceptionAttempting to load ESAPI.properties via the classpath.ESAPI.properties could not be loaded by any means. Fail. Exception was: java.lang.IllegalArgumentException: Failed to load ESAPI.properties as a classloader resource.

ESAPI is an open source used by Oracle Agile Web Client to protect Web application's security, avoid kinds of web attack like XSS, Injection, CSRF and so on. This API component must be present and...

Recover Keystore for Agile 9.3.2 and 9.3.3

agileks.jks is the Keystore used by Agile based on Java JCEKS and AES algorithm. So all the AES related password in Agile are associated with agileks.jks. During Agile 9.3.2/9.3.3 installation, a random Keystore password is created automatically, then agileks.jks is created as well based on the random Keystore password. After that Agile will use this Keystore password and Keystore file to encrypt the random Keystore password itself and save to Agile database propertytable table with format of "{AES}xxxx" like "{AES}sX+GBU67vmFlF9z7GcVBa/+qCyrfBL0YF61qOf1iUak=". It is displayed in JavaClient's Preference as "Keystore Password". In some cases the Keystore will be corrupted. For example, manually modify the Keystore Password in JavaClient or clone Agile database to destination Agile without updating Keystore file. Agile throws Keystore error during startup. AgileAuthenticationProviderImpl.initializelog4j:WARN No appenders could be found for logger (com.agile.util.sql.OracleConnectionImpl).log4j:WARN Please initialize the log4j system properly.java.io.IOException: Keystore was tampered with, or password was incorrect at com.sun.crypto.provider.JceKeystore.engineLoad(JceKeystore.java:867) at java.security.Keystore.load(Keystore.java:1214) at com.agile.util.crypto.ContainerCryptoUtil.loadKeystore(ContainerCryptoUtil.java:139) at com.agile.util.crypto.ContainerCryptoUtil.(ContainerCryptoUtil.java:77) at com.agile.admin.security.weblogic.WLSLoginModule.login(WLSLoginModule.java:193) at com.bea.common.security.internal.service.LoginModuleWrapper$1.run(LoginModuleWrapper.java:110) at java.security.AccessController.doPrivileged(Native Method) at com.bea.common.security.internal.service.LoginModuleWrapper.login(LoginModuleWrapper.java:106)... at weblogic.security.service.PrincipalAuthenticator.authenticate(PrincipalAuthenticator.java:338) at weblogic.security.service.CommonSecurityServiceManagerDelegateImpl.doBootAuthorization(CommonSecurityServiceManagerDelegateImpl.java:930) at weblogic.security.service.CommonSecurityServiceManagerDelegateImpl.initialize(CommonSecurityServiceManagerDelegateImpl.java:1054) at weblogic.security.service.SecurityServiceManager.initialize(SecurityServiceManager.java:873) at weblogic.security.SecurityService.start(SecurityService.java:148) at weblogic.t3.srvr.SubsystemRequest.run(SubsystemRequest.java:64) at weblogic.work.ExecuteThread.execute(ExecuteThread.java:256) at weblogic.work.ExecuteThread.run(ExecuteThread.java:221)*** Can not initialize key store from agileks.jks. Encryption service will fail.Error: Wrong Keystore password To recover Agile Keystore and everything related to AES password, follow below. 1. Get a new Keystore password You can give a new Keystore password at will by yourself. Be sure the password consists of alphabit and number and length is 8. For example "abcd1234". 2. Create a new Keystore file [oracle@jiechen-linux bin]$ pwd/u01/agile/agile932/agileDomain/bin[oracle@jiechen-linux bin]$ ./encryptPwdUtil.sh -genkeystore -storepass abcd1234Keystore is generated successfully in current directory with arguments: Keystore size: 200 Algorithm: AES Key size: 128 A new agileks.jks file will be created in AGILE_HOME/agileDomain/bin/ directory. Use keytool command to validate it. [oracle@jiechen-linux config]$ keytool -list -Keystore agileks.jks -storepass abcd1234 -storetype JCEKSKeystore type: JCEKSKeystore provider: SunJCEYour Keystore contains 200 entries{aes:128}fd06, Jan 21, 2015, SecretKeyEntry,{aes:128}a649, Jan 21, 2015, SecretKeyEntry,{aes:128}9e95, Jan 21, 2015, SecretKeyEntry,............ If see below error message, it means the Keystore file agileks.jks is invalid. [oracle@jiechen-linux config]$ keytool -list -Keystore agileks.jks -storepass abcd1234 -storetype JCEKSkeytool error: java.io.IOException: Keystore was tampered with, or password was incorrect Then you need to copy it to AGILE_HOME/agileDomain/config/ folder manually to overwrite the old one. 3. Encrypt the Keystore password and save to database [oracle@jiechen-linux bin]$ ./encryptDBSchemaPwd.sh abcd1234Encrypted DB password abcd1234 is:{AES}efw6EBEJWhFIQpIC1KSu7fMMb2T98Sjizk6LgfQM6oU= SQL to save to database update propertytable set value = '{AES}efw6EBEJWhFIQpIC1KSu7fMMb2T98Sjizk6LgfQM6oU=' where parentid=5004 and propertyid=1008;commit; 4. Re-encrypt below password db.password in agile.properties ifsuser password in server.conf superadmin password in boot.properties if required This topic applies to 9.3.2 and 9.3.3 only

agileks.jks is the Keystore used by Agile based on Java JCEKS and AES algorithm. So all the AES related password in Agile are associated with agileks.jks. During Agile 9.3.2/9.3.3 installation, a...

ClasspathLocator based on JMX to detect class location

Sometimes I get ClassNotFoundException error from Agile PLM and really do not know which jar file containing this Class is lost. Same happens if I get below error. java.lang.NoClassDefFoundError java.lang.ClassNotFoundException java.lang.NoSuchMethodException java.lang.NoSuchMethodError Another issue is if duplicated class are referenced by JVM, it is hard to to figure out which one is acting. Below is a simple tool written in Java with JMX to detect related class location in a working environment. Let me say its name ClasspathLocator JMX Service ClasspathLocator JMX MBean interface package com.agile.support.jmx;public interface ClasspathLocatorMBean {public String findLocation(String klass);} class to implement the ClasspathLocator JMX MBean interface package com.agile.support.jmx;import java.io.File;import java.net.URL;import java.net.URLDecoder;import java.security.CodeSource;import java.security.ProtectionDomain;public class ClasspathLocator implements ClasspathLocatorMBean {@Overridepublic String findLocation(String klass) {klass = klass.trim();try {Class clazz = Class.forName(klass);if (clazz == null) {return “Invalid class: ” + klass;}ProtectionDomain protectionDomain = clazz.getProtectionDomain();CodeSource codeSource = protectionDomain.getCodeSource();File jarFile;if (codeSource != null && codeSource.getLocation() != null) {jarFile = new File(codeSource.getLocation().toURI());} else {String path = clazz.getResource(clazz.getSimpleName() + “.class”).getPath();String jarFilePath = path.substring(path.indexOf(“:”) + 1, path.indexOf(“!”));jarFilePath = URLDecoder.decode(jarFilePath, “UTF-8”);jarFile = new File(jarFilePath);}return klass + “ -> ” + jarFile.getAbsolutePath();} catch (Throwable e) {e.printStackTrace();return klass + “ Not found”;}}} One Class file could exist as packed in a jar file or a physical *.class in a folder. So I use if-else to handle these two cases like above. ClasspathLocator JMX service package com.agile.support.jmx;import java.lang.management.ManagementFactory;import javax.management.MBeanServer;import javax.management.ObjectName;public class AgileSupportJMXAgent {private final MBeanServer mbs;public AgileSupportJMXAgent(){mbs = ManagementFactory.getPlatformMBeanServer();}public void register() throws Exception {ObjectName objectName = new ObjectName(“AgileSupport:type=ClasspathLocator”);if (mbs.isRegistered(objectName)){mbs.unregisterMBean(objectName);}ClasspathLocator cpLoc = new ClasspathLocator();mbs.registerMBean(cpLoc, objectName);}} In above code, I create a MBean folder named AgileSupport and the MBean name is ClasspathLocator. Trigger Class (Client) It is a client class to start ClasspathLocator JMX service. You can implement it simply by calling new AgileSupportJMXAgent().register() . However in Agile PLM, we can dynamically invoke it within Custom PX. In this case, I create CustomAction JmxPX.class and add it to Tools menu. package com.agile.support.jmx;import com.agile.api.IAgileSession;import com.agile.api.IDataObject;import com.agile.api.INode;import com.agile.px.ActionResult;import com.agile.px.ICustomAction;public class JmxPX implements ICustomAction {@Overridepublic ActionResult doAction(IAgileSession sess, INode node, IDataObject obj) {try {new AgileSupportJMXAgent().register();} catch (Exception e) {e.printStackTrace();return new ActionResult(ActionResult.EXCEPTION, e);}return new ActionResult(ActionResult.STRING, “AgileSupportJMXAgent started.”);}} Use Case When I do troubleshooting, I simply click ClassLocatorJMXService px in Tools menu, which I set up in JavaClient. Be sure to see the message AgileSupportJMXAgent started.. Next, I use JConsole to connect to Agile PLM JMX service, will see my customized ClasspathLocator MBean is listed correctly and the function findLocation() is available. For test purpose, I input oracle.jdbc.driver.OracleDriver and click button findLocation, it will pop up a window containing my expected result of jar location.

Sometimes I get ClassNotFoundException error from Agile PLM and really do not know which jar file containing this Class is lost. Same happens if I get below error. java.lang.NoClassDefFoundError java.lan...

LoginException: java.lang.SecurityException caused by Cookie Sharing

There is a very common issue which could impact all Agile PLM customers' integration that Agile SDK fails to create session and hits error " java.lang.SecurityException: User: e0FFxxxxxxx, failed to be authenticated". This article describes the scenario and call your attention to the fact that it is another issue than described in previous article Agile 9.3.2 URL PX error javax.security.auth.login.LoginException in Tomcat 6/7.Here is the scenario. There are two different system set up in same domain, TEST environment with server plmtest.sl.agilesoft.com and PRODUCTION environment with server plmprod.sl.agilesoft.com. TEST server has "cookie.domain=.sl.agilesoft.com" in agile.properties while PRODUCTION has same or "cookie.domain=.agilesoft.com" (In this article, we use .agilesoft.com for the example). User integrates URL-PX for PRODUCTION. Now end user first visit TEST server's WebClient and logon, then in the same browser he switches to PRODUCTION SERVER to logon and triggers the URL-PX. The URL-PX will randomly throws error like below.Error code : 60062Error message : Invalid username or passwordRoot Cause exception : javax.security.auth.login.LoginException: java.lang.SecurityException: User: e0FFUzoxMjh9QkQ3M0JFNTEzRjA1M0YxNDhCRjYwMDBERkJEMTYyRUQwMTdD, failed to be authenticated.at com.agile.api.common.WebLogicAuthenticator.login(WebLogicAuthenticator.java:78)at com.agile.api.pc.Session.authenticate(Session.java:1144)at com.agile.api.pc.Session.(Session.java:227)at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)at java.lang.reflect.Constructor.newInstance(Constructor.java:525)at com.agile.api.AgileSessionFactory.createSession(AgileSessionFactory.java:994)"User: e0FFUzoxMjh9QkQ3M0JFNTEzRjA1M0YxNDhCRjYwMDBERkJEMTYyRUQwMTdD" is a j_username from cookie because in URL-PX we use below sample code to retrieve browser session's cookie to connect to Agile.To analyze the issue we capture all the HTTP data from access of TEST and PRODUCTION WebClient and we find below behavior.When to logon TEST WebClient, we have these cookie settings for j_username and j_password.j_username=e0FFUzoxMjh9QkQ3M0JFNTEzRjA1M0YxNDhCRjYwMDBERkJEMTYyRUQwMTdD; j_password=JSUle0FFUzoxMjh9MDFBRDExQTM5MzdERUJCMjA2REU3MEI2ODRDN0ZEMDdDNEU3MTY4NzZDQkRFRDQ0OTIyNzg3RkYzQzdGMzI4RTA5MENERDkxNzQ5MEExRDM3Q0ZBNTFGRDNFOUJEREJGMDAzQTVBNURDRjE1MEM2N0U4NTIwQURFREExNjg4MDI1NzI1JSUldomain=.sl.agilesoft.comWhile to logon PRODUCTION WebClient, we have new cookies settingsj_username=e0FFUzoxMjh9RDRENzM1QTM5MjdDMUZDNTRFQjFGMzg5QkQ2RTk4RDQ2ODg1j_password=JSUle0FFUzoxMjh9RDM3RTU5QjQ2REY2MTUyRjY1RTZBMjZDN0M4MUUxODhGMEYyMjIxOTBBOTkwMzRBRDlENUNFNDFDN0U2MDkwMTk3QjA4NEJERERFQkNEMzM0QkE5REIxMkYzQzBGMTE2ODU2RkY2MzdBMzlCQUQzMzY1MkFFNjg4MjYyM0I0MjhCNTNFJSUldomain=.agilesoft.comAbove two groups of j_username/j_password are correct. However the browser will keep these two groups of cookies in the same HTTP Session as demonstrated in below screenshot. And if URL-PX is triggered from PRODUCTION server, above source code will get a wrong group of cookie. The root cause is all the browsers have almost the same feature that browser will share cookie across different windows.There are some workaround for IE, Chrome and Firefox to disable cookie sharing, but the recommended soluton is to divide different Agile servers into different subdomains, for example one in .sl.agilesoft.com and one in .pd.agilesoft.com, meanwhile set cookie.domain to its own, corresponding subdomain value in agile.properties.

There is a very common issue which could impact all Agile PLM customers' integration that Agile SDK fails to create session and hits error " java.lang.SecurityException: User: e0FFxxxxxxx, failed to...

ORA-01722 error if reference PPM ‘Created From Template’ in IQuery Where clause

There is one issue in Agile SDK API that if "General Info.Created From Template" of PPM is referenced in IQuery‘s WHERE clause, SDK client program will show "ORA-01722: invalid number" error. A BUG 19064118 is filed this week.This article describes why it happens and how to work around it. ScenarioThis is a customized SDK client program which uses IQuery to search some kinds of PPM objects with matched criteria. In WHERE clause, it has criteria: [General Info.Created From Template] in (‘PPM_template‘, ‘Project_Amy Template‘) This code is absolutely correct but when runs it immediately throws exception "ORA-01722" from execution of TableIterator.hasNext(). Root CauseThis is an Admin module design bug that "Created From Template" is wrongly defined to TEXT type in JavaClient Admin. Ideally it must be a Dynamica List pointing to Activity objects. However when to query the table ACTIVITY in database, it shows CREATED_FROM_TEMPLATE column is NUMBER type and stores only integer number. SQL> desc activityName Null Type ----------------------------- -------- -------------- ID NOT NULL NUMBER ...CREATED_FROM_TEMPLATE NUMBER ... When the client program runs, a SQL runs in Server with below similar syntax should get ORA-01722 as Oracle detects. select * from ACTIVITY where CREATED_FROM_TEMPLATE in (‘PPM_template‘, ‘Project_Amy Template‘); Since this is a Dynamica list and it points to Object lists, we should use nested criteria for List search. Then we revise code again. However we get "Unsupported operand datatype" error because Agile still thinks it is not a List type. A real chaos. WorkaroundNo BUG FIX available this moment. So only workaround is possible. Since CREATED_FROM_TEMPLATE column only stores integer number, we can input the template ID to the WHERE clause. If we revise the source code with mapped template id like below demonstrates, it works perfectly. So we need to find out the expected templates‘ IDs. There are two ways to achieve. The stupid way is to use SQL. SQL> select id, name from activity where decode(root_id, null,0, root_id)=0 and decode(parent_id, null, 0, parent_id)=0 and template=1 and name in (‘PPM_template‘, ‘Project_Amy Template‘);ID Name----------------------------15982 PPM_template15926 Project_Amy Template Another smart way is to dynamically find the expected templates‘ ID in another IQuery which will search templates with matched criteria, then pass the templates‘ IDs to WHERE clause of PPM Query. The Template‘s criteria should be: Project Template = ‘Template‘Root Parent is NULLName in (‘PPM_template‘, ‘Project_Amy Template‘) Finally we have a workable solution to work around the BUG.

There is one issue in Agile SDK API that if "General Info.Created From Template" of PPM is referenced in IQuery‘s WHERE clause, SDK client program will show "ORA-01722: invalid number" error. A...

SQL solution to show better Admin History data than JavaClient

In JavaClient, Admin History records all kinds of administration actions to system for audit. These actions could be Modify Class, Add List, Remove Workflow and more. But in most cases, Admin History does not display these actions clearly. For example below records confuse us. What object are they? Nobody can answer this question unless you analyze the database data internally.This article demonstrates how to customize a powerful SQL to list all kinds of actions in a self-explanatory way with these columns.loginid -- user's loginidaction -- user's action like "Create", "Modify"...object_type -- object type name, for example: attribute, class, list, personal criteria and etc.object.name -- object nameahd.details -- detailed action message in English languageah.created -- created timeAll the Admin history data are saved in admin_history and admin_history_details tables. admin_history_details table save detailed action message in different language. While admin_history is a mapping table to join agileuser tables with USERID, to admin nodes tables with OWNERID and to admin_history_details table with DETAILID.SQL> desc admin_historyName Null Type --------- -------- ------ ID NUMBER USERID NUMBER TIMESTAMP DATE ACTIONID NUMBER TYPE NUMBER OWNERID NUMBER DETAILID NUMBER CREATED NOT NULL DATE LAST_UPD NOT NULL DATE SQL> desc admin_history_detailsName Null Type -------- -------- ------------------ ID NUMBER LANGID NUMBER OBJECT VARCHAR2(600 CHAR) DETAILS CLOB CREATED NOT NULL DATE LAST_UPD NOT NULL DATE ACTIONID in admin_history table has 12 types of actions. They are: 1 : 'Create' 2 : 'Modify' 3 : 'Delete' 4 : 'SaveAs' 5 : 'CopyFrom' 6 : 'Undelete' 7 : 'Login' 8 : 'Reorder' 9 : 'Export' 10 : 'Purge' 11 : 'Import' 12 : 'Push' TYPE in admin_history has 3 kinds of nodes. They are: 0: all nodes in nodetable table 1: list data in listname table 2: personal criteria data in pers_criteria_node table We connect these 3 TYPE (3 tables) in a single table with UNION ALL in a sql, making it a single table: object .(select n.id, n.name, e.entryvalue object_type, 0 type_id from nodetable n, listentry e where n.objtype=e.entryid and e.parentid=101union allselect id, name, 'List' object_type, 1 type_id from listnameunion allselect id, name, 'PersonalCriteria' object_type, 2 type_id from pers_criteria_node) objectFinally, we join object table with admin_history, admin_history_details and agileuser tables. We have below.select u.loginid, case ah.actionidwhen 1 then 'Create'when 2 then 'Modify'when 3 then 'Delete'when 4 then 'SaveAs'when 5 then 'CopyFrom'when 6 then 'Undelete'when 7 then 'Login'when 8 then 'Reorder'when 9 then 'Export'when 10 then 'Purge'when 11 then 'Import'when 12 then 'Push'end action,object.object_type, object.name, ahd.details, ah.created from admin_history ah, admin_history_details ahd,(select n.id, n.name, e.entryvalue object_type, 0 type_id from nodetable n, listentry e where n.objtype=e.entryid and e.parentid=101union allselect id, name, 'List' object_type, 1 type_id from listnameunion allselect id, name, 'PersonalCriteria' object_type, 2 type_id from pers_criteria_node) object, agileuser u where ah.detailid=ahd.id and ah.type=object.type_id and langid=0 and ah.ownerid=object.id and u.id=ah.userid order by ah.created desc;Run it and we have a detailed Admin History result from SQL, absolutely better than JavaClient Admin History page. You will like it.

In JavaClient, Admin History records all kinds of administration actions to system for audit. These actions could be Modify Class, Add List, Remove Workflow and more. But in most cases, Admin History...

Set up Node Manager for Agile Cluster Servers

This article describes how to create Node Manager for Agile Cluster and manage them remotely. Suppose we have three Linux machines, one for Admin Server, the other two are for Managed Servers. Below is Agile Cluster information.# Machine slag9310w5c.mycompany.comWeblogic Server Location: /opt/bea/wlserver_10.3Admin Server Name: slag9310w5c-AgileServerListen Address: slag9310w5c.mycompany.com:7001Agile Domain location: /opt/agile0/agileDomain# Machine slag9310w5c-1.mycompany.comWeblogic Server Location: /opt/bea/wlserver_10.3Managed Server 1 Name: slag9310w5c-ManagedServer1Listen Address: slag9310w5c-1.mycompany.com:7001Agile Domain location: /opt/agile1/agileDomain# Machine slag9310w5c-2.mycompany.comWeblogic Server Location: /opt/bea/wlserver_10.3Managed Server 2 Name: slag9310w5c-ManagedServer2Listen Address: slag9310w5c-2.mycompany.com:7001Agile Domain location: /opt/agile2/agileDomainAdd machines to each cluster node# Node Manager for Admin Serverslag9310w5c --> slag9310w5c.mycompany.com:6666# Node Manager for Managed Server 1slag9310w5c-1 --> slag9310w5c-1.mycompany.com:6667# Node Manager for Managed Server 2slag9310w5c-2 --> slag9310w5c-2.mycompany.com:6668This need to be done from Weblogic Admin Console. We create 3 Machines and add listen address/port for each. These are used for Node Managers.And then assign each machine to corresponding server. For example we assign slag9310w5c machine to server slag9310w5c-AgileServer.We can review them in config.xml to confirm we are doing right.Configure Node managersOn each machine, we create its own Node Manager. We create these directories on each.# Admin Server‘s Node Manager location: /opt/agile0/nodemanager/slag9310w5c[oracle@slag9310w5c agile0]$ pwd/opt/agile0[oracle@slag9310w5c agile0]$ mkdir -p nodemanager/slag9310w5c# Managed Server 1‘s Node Manager location: /opt/agile1/nodemanager/slag9310w5c-1[oracle@slag9310w5c-1 agile1]$ pwd/opt/agile1[oracle@slag9310w5c-1 agile1]$ mkdir -p nodemanager/slag9310w5c-1# Managed Server 2‘s Node Manager location: /opt/agile2/nodemanager/slag9310w5c-2[oracle@slag9310w5c-2 agile2]$ pwd/opt/agile2[oracle@slag9310w5c-2 agile2]$ mkdir -p nodemanager/slag9310w5c-2Copy startNodeManager.sh from Weblogic Server to each Node Manager‘s directory.# On Admin Server‘s machine[oracle@slag9310w5c slag9310w5c]$ pwd/opt/agile0/nodemanager/slag9310w5c[oracle@slag9310w5c slag9310w5c]$ cp /opt/bea/wlserver_10.3/server/bin/startNodeManager.sh startNodeManager.shAnd modify its NODEMGR_HOME to point to its real Node Manager directory, and JAVA_OPTIONS to includes weblogic.nodemanager.ServiceEnabled=true which is to fix a BUG of Weblogic that nmStart command never returns to console.# On Admin Server‘s Node Manager dirctory, edit /opt/agile0/nodemanager/slag9310w5c/startNodeManager.shNODEMGR_HOME="/opt/agile0/nodemanager/slag9310w5c"JAVA_OPTIONS="${JAVA_OPTIONS} -Dweblogic.nodemanager.ServiceEnabled=true"Do same to 2 Managed Servers to create startNodeManager.sh and edit it. So we will have:# On Admin Server, /opt/agile0/nodemanager/slag9310w5c/startNodeManager.shNODEMGR_HOME="/opt/agile0/nodemanager/slag9310w5c"JAVA_OPTIONS="${JAVA_OPTIONS} -Dweblogic.nodemanager.ServiceEnabled=true"# On Managed Server 1, /opt/agile1/nodemanager/slag9310w5c-1/startNodeManager.shNODEMGR_HOME="/opt/agile1/nodemanager/slag9310w5c-1"JAVA_OPTIONS="${JAVA_OPTIONS} -Dweblogic.nodemanager.ServiceEnabled=true"# On Managed Server 2, /opt/agile2/nodemanager/slag9310w5c-2/startNodeManager.shNODEMGR_HOME="/opt/agile2/nodemanager/slag9310w5c-2"JAVA_OPTIONS="${JAVA_OPTIONS} -Dweblogic.nodemanager.ServiceEnabled=true"Create nodemanager.properties file in each Node Manager‘s directory.# On Admin Server, /opt/agile0/nodemanager/slag9310w5c/nodemanager.propertiesListenAddress=slag9310w5c.mycompany.comListenPort=6666SecureListener=falseStartScriptEnabled=trueStartScriptName=startServerAdminFromNM.shNativeVersionEnabled=trueCrashRecoveryEnabled=true# On Managed Server 1, /opt/agile1/nodemanager/slag9310w5c-1/nodemanager.propertiesListenAddress=slag9310w5c-1.mycompany.comListenPort=6667SecureListener=falseStartScriptEnabled=trueStartScriptName=startServerManaged1FromNM.shNativeVersionEnabled=trueCrashRecoveryEnabled=true# On Managed Server 2, /opt/agile2/nodemanager/slag9310w5c-2/nodemanager.propertiesListenAddress=slag9310w5c-2.mycompany.comListenPort=6668SecureListener=falseStartScriptEnabled=trueStartScriptName=startServerManaged2FromNM.shNativeVersionEnabled=trueCrashRecoveryEnabled=trueBy default, Node Manager will start startWeblogic.sh file. So we use StartScriptName to specify our own Agile Shell Script file. Make sure StartScriptEnabled is set to true. NativeVersionEnabled is used to enable emKill command to stop Agile Server remotely. CrashRecoveryEnabled enables Agile server to restart automatically if it crashes.From above we see NodeManager will execute startServerAdminFromNM.sh for Admin Server, startServerManaged1FromNM.sh for Managed Server 1 and startServerManaged2FromNM.sh for Managed Server 2. These three files do not exist. So we need to create them. Just copy from the Shell Script in agileDomain/bin/ folder.# Admin Server /opt/agile0/agileDomain/bin/ directory[oracle@slag9310w5c bin]$ cp startServerAgileAdmin.sh startServerAdminFromNM.sh# Managed Server 1 /opt/agile1/agileDomain/bin/ directory[oracle@slag9310w5c-1 bin]$ cp startServerAgileManaged1.sh startServerManaged1FromNM.sh# Managed Server 2 /opt/agile2/agileDomain/bin/ directory[oracle@slag9310w5c-2 bin]$ cp startServerAgileManaged2.sh startServerManaged2FromNM.shBecause Node Manager invokes these SH file from its own process, we need to edit these three files to use absolute Path for setEnv.sh execution.# Admin Server /opt/agile0/agileDomain/bin/startServerAdminFromNM.sh#. ./setEnv.sh -- comment it#cd .. -- comment it. /opt/agile0/agileDomain/bin/setEnv.sh# Managed Server 1 /opt/agile1/agileDomain/bin/startServerManaged1FromNM.sh#. ./setEnv.sh -- comment it#cd .. -- comment it. /opt/agile1/agileDomain/bin/setEnv.sh# Managed Server 2 /opt/agile2/agileDomain/bin/startServerManaged2FromNM.sh#. ./setEnv.sh -- comment it#cd .. -- comment it. /opt/agile2/agileDomain/bin/setEnv.shAnd be sure to add weblogic.nodemanager.ServiceEnabled=true before "weblogic.server". For example:"$JAVA_HOME/bin/java" ... ... -Dweblogic.nodemanager.ServiceEnabled=true weblogic.ServerEnroll each node manager to agileDomainThis needs to be done one Admin Server, 2 Managed Servers one by one. Before enroll, the Admin Server must be started manually.Invoke Agile‘s sentEnv.sh to setup runtime environment[oracle@slag9310w5c bin]$ pwd/opt/agile0/agileDomain/bin[oracle@slag9310w5c bin]$ source setEnv.shYour environment has been set.Run weblogic.WLST program[oracle@slag9310w5c bin]$ java weblogic.WLSTInitializing WebLogic Scripting Tool (WLST) ...Jython scans all the jar files it can find at first startup. Depending on the system, this process may take a few minutes to complete, and WLST may not return a prompt right away.Welcome to WebLogic Server Administration Scripting ShellType help() for help on available commandsUse connect command to connect to Admin Serverwls:/offline> connect(‘superadmin‘, ‘agile‘, ‘t3://slag9310w5c.mycompany.com:7001‘)Connecting to t3://slag9310w5c.mycompany.com:7001 with userid superadmin ...Successfully connected to Admin Server ‘slag9310w5c.mycompany.com-AgileServer‘ that belongs to domain ‘agileDomain‘.Warning: An insecure protocol was used to connect to the server. To ensure on-the-wire security, the SSL port or Admin port should be used instead.Enroll Admin Server‘s Node Manager to agileDomainwls:/agileDomain/serverConfig> nmEnroll(‘/opt/agile0/agileDomain‘, ‘/opt/agile0/nodemanager/slag9310w5c‘)Enrolling this machine with the domain directory at /opt/agile0/agileDomain ...Successfully enrolled this machine with the domain directory at /opt/agile0/agileDomain.wls:/agileDomain/serverConfig> exit()Exiting WebLogic Scripting Tool.Now we have Admin Server‘s Node Manager successfully enrolled to agileDomain. Next we need to do same on Managed Server‘s host with same steps.source setEnv.shjava weblogic.WLSTconnect(‘superadmin‘, ‘agile‘, ‘t3://slag9310w5c.mycompany.com:7001‘)nmEnroll(‘/opt/agile1/agileDomain‘, ‘/opt/agile1/nodemanager/slag9310w5c-1‘) # on Managed Server 1 hostnmEnroll(‘/opt/agile2/agileDomain‘, ‘/opt/agile2/nodemanager/slag9310w5c-2‘) # on Managed Server 2 hostStart each Node ManagerLogon Admin Server, start its Node Manager.[oracle@slag9310w5c slag9310w5c]$ pwd/opt/agile0/nodemanager/slag9310w5c[oracle@slag9310w5c slag9310w5c]$ ./startNodeManager.sh...... Jul 24, 2014 11:06:39 PM weblogic.nodemanager.server.Listener runINFO: Plain socket listener started on port 6,666, host slag9310w5c.mycompany.comNeed to do same on each Managed Server and start their own Node Manager.Start Agile Server from remote machineAfter Node Manager of Admin Server is started successfully, we can start the Admin Server from other machines which has been installed Weblogic Server, using WLST script. nmConnect: connect to remote Node ManagernmStart: start remote Weblogic ServernmServerStatus: check remote server statusnmKill: stop remote Weblogic ServerFor example I am starting Agile Admin Server from my local Windows machine by connecting to Admin's Node Manasger.Note: The password of Node Manager is different from Admin Console Logon password. Node Manager password is defined separately.C:\Oracle\Middleware\wlserver_12.1\server\bin>setWLSEnv.cmdYour environment has been set.C:\Oracle\Middleware\wlserver_12.1\server\bin>java weblogic.WLSTInitializing WebLogic Scripting Tool (WLST) ...Welcome to WebLogic Server Administration Scripting ShellType help() for help on available commandswls:/offline> nmConnect(‘superadmin‘, ‘agile‘, ‘slag9310w5c.mycompany.com‘, ‘6666‘, ‘agileDomain‘,‘/opt/agile0/agileDomain‘,‘plain‘)Connecting to Node Manager ...Successfully Connected to Node Manager.wls:/nm/agileDomain> nmStart(‘slag9310w5c.mycompany.com-AgileServer‘)Starting server slag9310w5c.mycompany.com-AgileServer ...Successfully started server slag9310w5c.mycompany.com-AgileServer ...wls:/nm/agileDomain> nmServerStatus(‘slag9310w5c.mycompany.com-AgileServer‘)RUNNINGwls:/nm/agileDomain> nmKill(‘slag9310w5c.mycompany.com-AgileServer‘)Killing server slag9310w5c.mycompany.com-AgileServer ...Successfully killed server slag9310w5c.mycompany.com-AgileServer ...We can see the execution from Node Manager log INFO: Starting WebLogic server with command line: /opt/agile0/agileDomain/bin/startServerAdminFromNM.sh Jul 24, 2014 11:07:15 PM weblogic.nodemanager.server.ServerManager logINFO: Server output log file is ‘/opt/agile0/agileDomain/servers/slag9310w5c.mycompany.com-AgileServer/logs/slag9310w5c.mycompany.com-AgileServer.out‘startServerAdminFromNM.sh is called by Node Manager and server log is written to slag9310w5c.mycompany.com-AgileServer.out file.To start Agile Managed Servers from remote machine, use the same command with corresponding parameters. For example starting Managed Server 1, we have commands:setWLSEnv.cmdjava weblogic.WLSTnmConnect(‘superadmin‘, ‘agile‘, ‘slag9310w5c-1.mycompany.com‘,‘6667‘, ‘agileDomain‘,‘/opt/agile1/agileDomain‘,‘plain‘)nmStart(‘slag9310w5c-1.mycompany.com‘)nmServerStatus(‘slag9310w5c-1.mycompany.com‘)nmKill(‘slag9310w5c-1.mycompany.com‘)Another way to start Managed Servers, we can logon Weblogic Console web page to start them from Start button, showing in below screenshot.

This article describes how to create Node Manager for Agile Cluster and manage them remotely. Suppose we have three Linux machines, one for Admin Server, the other two are for Managed Servers. Below...

BASIC_LEXER and WORLD_LEXER in Agile Quick Search

We all know Agile Quick Search uses Oracle Text to do search based on the object name and description. For BASIC_LEXER, Agile defines several special characters in CONTENT LEXER, making them as normal characters not delimiter. While for WORLD_LEXER, Agile has no such definition. So recently we have one customer reporting that if to search a keyword, no expected result returns. While search the same keyword wrapped with parentheses, the object returns in result. The user case is: Create an Item, input its Description with content: "hello (validation)" . Do Quick Search against Item type with keyword "validation", no result. With keyword "(validation)", the Item shows in result. Let's see how Agile Quick Search works with Oracle Text. Root Cause We enable the DEBUG for SQL and get the Quick Search SQL which is mostly like below with CONTAINS syntax. SELECT A.ID,A.CLASS,A.SUBCLASS, A.FLAGS, A.REV_FLAGS,NULL, NULL, A.ITEM_NUMBER, A.ITEM_NUMBER, A.DESCRIPTION,A.DESC_REV , A.RELEASE_TYPE, A.REV_NUMBER,A.LATEST_RELEASED_ECO , A.SUBCLASS,A.RELEASE_DATE,A.MULTILIST03,A.SUBCLASS,A.CREATE_USER,A.MULTILIST01,A.RELEASE_DATE,A.RELEASE_TYPE, A.CREATE_USER , 0, 0 , A.ITEM_NUMBER FROM ITEM_P2P3_QUERY A WHERE (((NVL(A.DELETE_FLAG, 0) = 0) AND CONTAINS(A.DESCRIPTION,'VALIDATION%',0) > 0 ) ) " Definitely ITEM_P2P3_QUERY is a view. So we need to find out the DESCRIPTION column''s concrete table. --get ITEM_P2P3_QUERY definitionSQL> select view_name, text from user_views where view_name = 'ITEM_P2P3_QUERY'; We get the table name: ITEM. Next we get DESCRIPTION based indexes. SQL> column index_name format a15;SQL> column table_name format a15;SQL> column column_name format a15;SQL> select index_name , table_name , column_name from user_ind_columns where table_name='ITEM' and column_name='DESCRIPTION' ;INDEX_NAMETABLE_NAMECOLUMN_NAME--------------- --------------- ---------------ITEM_CTX_IDXITEMDESCRIPTIONITEM_DESC_IDXITEMDESCRIPTION Since the SQL uses CONTAINS syntax, obviously the ITYP_OWNER is CTXSYS. So we get the correct index name: ITEM_CTX_IDX column index_name format a15;column index_type format a15;column ityp_owner format a15;column ityp_name format a15;select index_name, index_type, ityp_owner, ityp_name from USER_INDEXES where index_name in ('ITEM_DESC_IDX', 'ITEM_CTX_IDX');INDEX_NAMEINDEX_TYPEITYP_OWNERITYP_NAME--------------- --------------- --------------- ---------------ITEM_CTX_IDXDOMAINCTXSYSCONTEXTITEM_DESC_IDXNORMAL Next, we need to get the detailed definition of index ITEM_CTX_IDX. SQL> select ctx_report.create_index_script('ITEM_CTX_IDX') from dual; CTX_REPORT.CREATE_INDEX_SCRIPT('ITEM_CTX_IDX')--------------------------------------------------------------------------------begin ctx_ddl.create_preference('"ITEM_CTX_IDX_DST"','MULTI_COLUMN_DATASTORE'); ctx_ddl.set_attribute('"ITEM_CTX_IDX_DST"','COLUMNS','ITEM_NUMBER,DESCRIPTION');end;/begin ctx_ddl.create_preference('"ITEM_CTX_IDX_FIL"','NULL_FILTER');end;/begin ctx_ddl.create_section_group('"ITEM_CTX_IDX_SGP"','BASIC_SECTION_GROUP');end;/begin ctx_ddl.create_preference('"ITEM_CTX_IDX_LEX"','BASIC_LEXER'); ctx_ddl.set_attribute('"ITEM_CTX_IDX_LEX"','PRINTJOINS','_-~*'@#%^&.()+=:";');end;/begin ctx_ddl.create_preference('"ITEM_CTX_IDX_WDL"','BASIC_WORDLIST'); ctx_ddl.set_attribute('"ITEM_CTX_IDX_WDL"','STEMMER','ENGLISH'); ctx_ddl.set_attribute('"ITEM_CTX_IDX_WDL"','FUZZY_MATCH','GENERIC');end;/begin ctx_ddl.create_stoplist('"ITEM_CTX_IDX_SPL"','BASIC_STOPLIST');end;/begin ctx_ddl.create_preference('"ITEM_CTX_IDX_STO"','BASIC_STORAGE'); ctx_ddl.set_attribute('"ITEM_CTX_IDX_STO"','R_TABLE_CLAUSE','lob (data) store as (cache)'); ctx_ddl.set_attribute('"ITEM_CTX_IDX_STO"','I_INDEX_CLAUSE','compress 2');end;/begin ctx_output.start_log('ITEM_CTX_IDX_LOG');end;/create index "AG932_CUSTOMER1"."ITEM_CTX_IDX" on "AG932_CUSTOMER1"."ITEM" ("DESCRIPTION") indextype is ctxsys.context parameters(' datastore "ITEM_CTX_IDX_DST" filter "ITEM_CTX_IDX_FIL" section group "ITEM_CTX_IDX_SGP" lexer "ITEM_CTX_IDX_LEX" wordlist "ITEM_CTX_IDX_WDL" stoplist "ITEM_CTX_IDX_SPL" storage "ITEM_CTX_IDX_STO" ')/begin ctx_output.end_log;end;/ From above result, we get the important information that the index uses BASIC_LEXER as default lexer for English and all other supported whitespace delimited languages. begin ctx_ddl.create_preference('"ITEM_CTX_IDX_LEX"','BASIC_LEXER'); ctx_ddl.set_attribute('"ITEM_CTX_IDX_LEX"','PRINTJOINS','_-~*'@#%^&.()+=:";');end;/ Here Agile defines "()" as one of PRINTJOINS, which means it will be treated as a normal alphanumeric character and stored in TEXT index. So "(validation)" is an entire word, instead "validation" is not. How it is defined When we install Agile database, Agile will first creates an OBJECT_LEXER as BASIC_LEXER, and includes predefined printjoins. It is in ctxsys owner. --agile9_fts_prefs_lexer_basic.sqlbeginctx_ddl.create_preference('OBJECT_LEXER', 'BASIC_LEXER');ctx_ddl.set_attribute('OBJECT_LEXER', 'printjoins', '_-~*''@#%^&.()+=:";');end;/ Then schema user creates the TEXT index based on the CTXSYS.OBJECT_LEXER. --agile9_ctx_recreate.sqlCREATE INDEX ITEM_CTX_IDX ON ITEM(DESCRIPTION) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('DATASTORE CTXSYS.ITEM_MULTI_PREF LEXER CTXSYS.OBJECT_LEXER SECTION GROUP CTXSYS.OBJECT_SECTION_GROUP STOPLIST CTXSYS.EMPTY_STOPLIST'); One more thing, ITEM_CTX_IDX indexes data from both ITEM_NUMBER and DESCRIPTION columns. --agile9_fts_prefs.sqlbeginctx_ddl.create_preference('ITEM_MULTI_PREF', 'MULTI_COLUMN_DATASTORE');ctx_ddl.set_attribute('ITEM_MULTI_PREF', 'COLUMNS', 'ITEM_NUMBER,DESCRIPTION');end;/ Solution Two options. Use *validation* to search Recreate the TEXT index with WORLD_LEXER ctxsys@agile9_fts_prefs.sql ctxsys@agile9_fts_prefs_lexer_world.sql schemauser@agile9_ctx_recreate.sql

We all know Agile Quick Search uses Oracle Text to do search based on the object name and description. For BASIC_LEXER, Agile defines several special characters in CONTENT LEXER, making them as...

An example to show how to use 3rd party tools to diagnose Agile PLM LDAP problem

If all LDAP configuration set up correctly in Agile PLM and expected LDAP user accounts synchronized to system as well, but ldap user is still not able to logon Agile system, it may be caused by wrong setting on LDAP Server itself.This article demonstrates how to use different kinds of 3rd party tools to diagnose such LDAP authentication issue. We usually get below error trace from Agile PLM server log, but it does not help to us because the real error message (and error code) is wrapped and customized by Agile, and invisible to external. Login failed for user : fg3bvjActual message : Authentication Failed. Please make sure the username and password are correct! Job was cancelled due to this error!14/07/09 00:48:11 Authentication Failed. Please make sure the username and password are correct! Job was cancelled due to this error!14/07/09 00:48:11 com.agile.util.exception.CMAppException: Authentication Failed. Please make sure the username and password are correct! Job was cancelled due to this error!14/07/09 00:48:11 at com.agile.admin.ldap.DirService.getFailOverDirContext(DirService.java:300)14/07/09 00:48:11 at com.agile.admin.ldap.DirService.checkUserAuthentication(DirService.java:1480)14/07/09 00:48:11 at com.agile.admin.ldap.DirService.validateCredentials(DirService.java:246)In this case, we have to use Wireshark (or tcpdump on Linux) to collect all TCP package data for troubleshooting. First, we validate if Agile hands over the right ldap user account with correct password to LDAP Server.From above screenshot we know yes it is.As an Acknowledge response to package 7702, the package 7703 contains the feedback of authentication from LDAP Server. And it says invalidCredentials(49) which means LDAP Server rejects the authentication internally.LDAPMessage bindResponse(1) invalidCredentials (80090308: LdapErr: DSID-0C0903A9, comment: AcceptSecurityContext error, data 531, v1db1)We have the error data 531 which is defined by LDAP Server vendor only. Since this is Microsoft Active Directory, we need to go to Microsoft to analyze the error code.We download the Err program from link http://www.microsoft.com/en-us/download/details.aspx?id=985 and get the error detailed explanation.err 0x531# for hex 0x531 / decimal 1329 : ERROR_INVALID_WORKSTATION winerror.h# Logon failure: user not allowed to log on to this computer.# 1 matches found for "0x531"The "computer" here is not the user‘s working machine, it is the Agile Application Server machine where Agile communicates with LDAP server in back end. In this scenario, the Agile server is Linux. It could be the problem that the LDAP Server does not allow user to logon AD from a Linux machine.If go through all the AD attribute on Microsoft website http://msdn.microsoft.com/en-us/library/ms680868(v=vs.85).aspx , we get the attribute User-Workstations and it saysUser-Workstations attributeContains the NetBIOS or DNS names of the computers running Windows NT Workstation or Windows 2000 Professional from which the user can log on. Each NetBIOS name is separated by a comma. Multiple names should be separated by commas.Again we use another tool Softera LDAP Browser to get the attributes definition of the problematic user "fg3bvj" and get below value.Absolutely the AD Administrator prevents user from logon AD on other unauthenticated machines. Solution is to add the Agile Server linux host name to userWorkstations or remove its all the values.

If all LDAP configuration set up correctly in Agile PLM and expected LDAP user accounts synchronized to system as well, but ldap user is still not able to logon Agile system, it may be caused by wrong...

How to integrate OpenLDAP with Agile PLM

Agile supports other kinds of LDAP serve as a Generic LDAP node in system with the customized groovy script to map LDAP Server‘s attributes to Agile‘s. The difficulty of integration for Agile Administrator is to understand LDAP specific attributes and write the correct groovy code. This article describes the principle of integration with OpenLDAP, an open-source LDAP server, with a sample of scripts. After setting up the OpenLDAP Server, the administrator needs to import all corporation‘s employees data into LDAP. These data must follow the LDIF standard document RFC2849. Suppose we have the Base DN of people.company.com, so in our scenario we define three LDAP users ldapjie1, ldapjie2 and ldapjie3 under the OU of people.company.com in a Import file. Attention that we do not define any Group in this sample because I do not know how to do as I am not LDAP Administrator. # all_users.ldifdn: cn=ldapjie1, ou=People, dc=company,dc=comtelephoneNumber: 123-456-7890facsimileTelephoneNumber: 123-456-7980objectClass: topobjectClass: personobjectClass: organizationalPersontitle: engineersn: chencn: ldapjie1dn: cn=ldapjie2, ou=People, dc=company,dc=comtelephoneNumber: 123-456-78912facsimileTelephoneNumber: 123-456-79812objectClass: topobjectClass: personobjectClass: organizationalPersontitle: engineersn: chencn: ldapjie2dn: cn=ldapjie3, ou=People, dc=company,dc=comtelephoneNumber: 123-456-789123facsimileTelephoneNumber: 123-456-798123objectClass: topobjectClass: personobjectClass: organizationalPersontitle: engineersn: chencn: ldapjie3 Then we use the 3rd party tool JXplorer to import this file into OpenLDAP server and we will see them imported successfully. Double click anyone user, then click Button "Properties", we will see the user‘s all available attributes. Next we need to set up JavaClient to input OpenLDAP specific data. You many have different values than mine. So you shall not copy them directly. Agent: GenericLDAPURL: ldap://ldapserver.company.com:389Domain: company.comUsername: cn=ldapjie3,ou=People,dc=company,dc=comUser Path: ou=People, dc=company,dc=comSearch Scope: SUB_TREESearch Filter: (objectclass=person) Customized Groovy script: import javax.naming.directory.*def getSchemaInfo() { [ classUser: "person", entryDN: "entryDN", entryCN: "cn", entryGUID: "entryUUID", userID: "cn", #firstName: "givename", lastName: "sn", title: "title", workPhone: "telephoneNumber", fax: "facsimileTelephoneNumber", createTimestamp: "createTimestamp", modifyTimestamp: "modifyTimestamp", dateFormat: "yyyyMMddHHmmss‘Z‘", sizeLimit: 1000 ]}def isAccountDisabled(Attributes attributes) { return false}def getEntryDN(SearchResult entry) { Attributes attrs = entry.getAttributes() Attribute dnAttr = attrs.get("entryDN") return dnAttr.get()} In getSchemaInfo() function, the attributes in left are Agile‘s hard-coded attributes. The right attributes are from OpenLDAP which are shown in the second screenshot. classUser: mapping to OpenLDAP‘s objectClass "person" entryDN: it is DN, mapping to entryDN. If use other LDAP Browser, this attribute is invisible. entryCN: it is common name, mapping to cn entryGUID: it should be a GUID mapped value in agileuser table, but due to Agile bug, GUID will not populate if LDAP server is GenericLDAP. userID: common name firstname: there is no givename in this sample because LDIF standard does not define it. lastname: mapping to Surname "sn" createTimestamp: If use other LDAP Browser, this attribute is invisible. modifyTimestamp: If use other LDAP Browser, this attribute is invisible. In isAccountDisabled() function, because there is no attribute similar to "account disabled", so we just directly return false, supposing all users are active. In getEntryDN() function, we return "entryDN", the DN‘s value. In other LDAP server, this attribute may have different name. After javaClient setup, Weblogic Console must have OpenLDAP Authenticator provider configured. All the setting is same as ActiveDirectory except the "User Name Attribute:cn". After restart Weblogic, you should be able to see users synced from OpenLDAP in console. Go back to JavaClient to do Preview and Sync. Agile will import all these users into agileuser table. Next all these users are able to logon Agile. Note: migrateUsersToDB tool does not support GenericLDAP

Agile supports other kinds of LDAP serve as a Generic LDAP node in system with the customized groovy script to map LDAP Server‘s attributes to Agile‘s. The difficulty of integration for...

Solution to detect list attribute references

Agile shows alert and does not allow to modify List attribute to switch to another List if this attribute is already referenced by below criteria. Admin Criteria Report Criteria Search Criteria To enable the attribute modification, we have to go to these Criteria and remove the references one by one. But usually in the alert window we cannot identify the criteria is of Admin, Report or Search. Also if it is used by Search, the alert does not show us who owns the Search. Below screenshot will confuse us. Admin Criteria Say we have "Change Orders.Page Two" list attribute named "JieListAttribute" and links to a List "test000001". Now we create a Criteria named "JieCriteria", add this list attribute to criteria mask like below. Note: Do not select "IS NULL" or "IS NOT NULL" because these two do not impact attribute's modification. Go back to attribute "JieListAttribute", try to select any list for it and click Save, you will get alert. To detect Admin Criteria references, we can use below SQL to find out. When it prompts for ATTRIBUTE_ID and CLASS_NAME, input its BASE ID and class name (case sensitive). SET LIN 200COLUMN class_id format a15COLUMN criteria_id format a20COLUMN criteria_name format a30COLUMN class_name format a30SELECT criteria_id || '' criteria_id, criteria_name, class_id, class_nameFROM (SELECT a.id criteria_id, a.description criteria_name, b.value class_id FROM nodetable a, propertytable b WHERE a.parentid = 3642 AND a.id = b.parentid AND b.propertyid = 53 AND a.id IN (SELECT DISTINCT parentid FROM admincriteria WHERE attid = &ATTRIBUTE_ID AND relop NOT IN ( 9, 10 ))) criterias, (SELECT n.id cid, n.description class_name FROM nodetable n, (SELECT id FROM nodetable WHERE description = '&CLASS_NAME' AND objtype IN ( 5, 13 )) nc WHERE n.id = nc.id OR n.parentid IN (SELECT id FROM nodetable WHERE objtype = 14 AND parentid = nc.id)) classesWHERE classes.cid = criterias.class_id;SQL>/Enter value for attribute_id: 1271old 15: WHERE attid = &ATTRIBUTE_IDnew 15: WHERE attid = 1271Enter value for class_name: Change Ordersold 22: WHERE description = '&CLASS_NAME'new 22: WHERE description = 'Change Orders'CRITERIA_ID CRITERIA_NAME CLASS_ID CLASS_NAME-------------------- ------------------------------ --------------- ------------------------------2474421 JieCriteria 6000 Change Orders Report Criteria Let's create a Custom Report as any user on WebClient, define its Query Definition with "Change Orders.Page Two.JieListAttribute In (xxxxx)". Then try to modify the attribute's list to others on JavaClient, we will get same alert. Solution SET LIN 200COLUMN report_id format a10COLUMN report_name format a40COLUMN query_id format a10SELECT report_id || '' report_id, report_name, query_id || '' query_idFROM (SELECT a.id report_id, A.NAME report_name, B.TYPE report_class_id, b.id query_id FROM REPORT A, QUERY B WHERE A.CRITERIA_ID = B.ID AND B.ID IN (SELECT DISTINCT QUERY_ID FROM CRITERIA WHERE ATTR_ID = &ATTRIBUTE_ID AND RELATIONAL_OP NOT IN ( 9, 10 ))) reports, (SELECT n.id cid, n.description class_name FROM nodetable n, (SELECT id FROM nodetable WHERE description = '&CLASS_NAME' AND objtype IN ( 5, 13 )) nc WHERE n.id = nc.id OR n.parentid IN (SELECT id FROM nodetable WHERE objtype = 14 AND parentid = nc.id)) classesWHERE reports.report_class_id = classes.cid;SQL>/Enter value for attribute_id: 1271old 13: WHERE ATTR_ID = &ATTRIBUTE_IDnew 13: WHERE ATTR_ID = 1271Enter value for class_name: Change Ordersold 20: WHERE description = '&CLASS_NAME'new 20: WHERE description = 'Change Orders'REPORT_ID REPORT_NAME QUERY_ID---------- ---------------------------------------- ----------14325816 Can you guess who I am? (d) 1432581714325809 Can you guess who I am? (c) 14325811 Then you can open the Report object and modify the Query Definition to remove the reference, if you have the privilege. Search Criteria On WebClient, create an Advanced Search, set criteria to use the same attribute. We will get same error if we go to JavaClient to modify the attribute. Solution SET LIN 200COLUMN loginid format a15COLUMN query_name format a40COLUMN query_id format a10SELECT u.loginid, query_id || '' query_id, query_nameFROM (SELECT A.ID query_id, A.TYPE query_class_id, A.NAME query_name, A.owner ownerid FROM QUERY A WHERE A.ID IN (SELECT DISTINCT QUERY_ID FROM CRITERIA WHERE ATTR_ID = &ATTRIBUTE_ID AND RELATIONAL_OP NOT IN ( 9, 10 ))) queries, (SELECT n.id cid, n.description class_name FROM nodetable n, (SELECT id FROM nodetable WHERE description = '&CLASS_NAME' AND objtype IN ( 5, 13 )) nc WHERE n.id = nc.id OR n.parentid IN (SELECT id FROM nodetable WHERE objtype = 14 AND parentid = nc.id)) classes, agileuser uWHERE queries.query_class_id = classes.cid AND queries.ownerid = u.id AND (query_name is null OR query_id || '' <> query_name); sql>/ Enter value for attribute_id: 1271old 11: WHERE ATTR_ID = &ATTRIBUTE_IDnew 11: WHERE ATTR_ID = 1271Enter value for class_name: Change Ordersold 18: WHERE description = '&CLASS_NAME'new 18: WHERE description = 'Change Orders'LOGINID QUERY_ID QUERY_NAME--------------- ---------- ----------------------------------------admin 14321593admin 14325249admin 14325817 admin1401373750229admin 14325848 Can you guess who I am? (a)admin 14325896 Can you guess who I am? (b) You can ask the user "admin" to modify his Search criteria. Note: if query_name is null or the format is like loginid plus a number like "admin1401373750229", it means they are temporary Advanced Search. They must be deleted manually via SQL: delete query where id=&query_id;delete criteria where query_id=&query_id;delete select_list where query_id=&query_id;commit;

Agile shows alert and does not allow to modify List attribute to switch to another List if this attribute is already referenced by below criteria. Admin Criteria Report Criteria Search Criteria To...

Allow agile user to login Weblogic Admin Console

By default, Agile uses the default user superadmin as the administrator user to start Weblogic and logon Admin Console. We also can enable any type of users to start Weblogic or manage it, for example the DB user and LDAP user. DB User to start WLS and logon WLS console LDAP user to logon WLS console Note: As we talked in previous article Agile superadmin authentication during Weblogic startup, user authentication during WLS startup is based on the DB connection, so LDAP user is not feasible to start WLS. DB User to start WLS and logon WLS console To allow DB user to start and login Weblogic, we only modify agile.properties and boot.properties file. Note if enable wls.admin.console.users in agile.properties file will override the user account in boot.properties. That is to say, if superadmin user to start WLS but also other user to logon WLS console, add both superadmin and other users to wls.admin.console.users parameters like below. ##agile.propertieswls.admin.console.users =superadmin;admin;jiechen If other user to start and logon WLS console, need to modify both files to remove superadmin as below. ##agile.propertieswls.admin.console.users =admin;jiechen ##boot.propertiesusername=adminpassword=agile9 LDAP user to logon WLS console Each user in wls.admin.console.users will be added to WLS subject as separate principle and must be validated through WLSLoginModule.class which only works as DB authentication. So if add one ldap user to wls.admin.console.users and expect this user to logon WLS console will get 403--Forbidden error. Error 403--ForbiddenFrom RFC 2068 Hypertext Transfer Protocol -- HTTP/1.1:10.4.4 403 ForbiddenThe server understood the request, but is refusing to fulfill it. Authorization will not help and the request SHOULD NOT be repeated. ... This status code is commonly used when the server does not wish to reveal exactly why the request has been refused, or when no other response is applicable. And in WLS server log the detailed error is: java.lang.NullPointerException at com.agile.util.Scrambler.getHashAlgorithm(Scrambler.java:107) at com.agile.admin.security.userregistry.DBUserAdapter.checkPassword(DBUserAdapter.java:89) at com.agile.admin.security.userregistry.DBUserAdapter.validateCredentials(DBUserAdapter.java:858) at com.agile.admin.security.weblogic.WLSLoginModule.validate(WLSLoginModule.java:477) at com.agile.admin.security.weblogic.WLSLoginModule.login(WLSLoginModule.java:199) at com.bea.common.security.internal.service.LoginModuleWrapper$1.run(LoginModuleWrapper.java:110) at java.security.AccessController.doPrivileged(Native Method) at com.bea.common.security.internal.service.LoginModuleWrapper.login(LoginModuleWrapper.java:106) To make it feasible we have to setup on WLS console manually after you successfully set up LDAP Authentication provider in WLS.First logon WLS console as the DB user, go to Security Realms >AgileRealm >Realm Roles, then expand Global Roles || Roles || Admin, click View Role Conditions link. Add a new OR condition "User ldapuser1" with Administrators group. Enable the modification, then user ldapuser1 is able to logon WLS console. Additionally, if add a new OR condition "Group LDAP Group 1", the all the ldap users in the Group of "LDAP Group 1" are allowed to logon as well.

By default, Agile uses the default user superadmin as the administrator user to start Weblogic and logon Admin Console. We also can enable any type of users to start Weblogic or manage it, for example...

Agile superadmin authentication during Weblogic startup

In Agile PLM, we all know superadmin account is used to start Weblogic server, this account is authenticated against Agile database. In previous version before 9.3.2, we define the username and its plain password in startAgile script. In 9.3.2 and 9.3.3, we move the user account information to a separate file which is specified by -Dweblogic.system.BootIdentityFile parameter, of course the username and password are encrypted. Since it is a database authentication, by default we shall use SQLAuthenticator to do user validation in Weblogic Security, but Agile uses its own security provider, that is AgileAuthenticator, a customized extension. Let's see how it works during Weblogic start up. AgileAuthenticator From config.xml, we see the default authenticator is agile-authenticatorType. The xsi type is ext, not wls, and xsd implements weblogic/security/extension, not weblogic/security. It is a "unnamed" authentication provider (with no sec:name definition), so Weblogic will lookup a provider named "AgileAuthenticator". The definition of AgileAuthenticator could be found in agileSecurityProviders.jar file which locates in WLS_HOME/server/lib/mbeantypes/. If extract this file, we will find this provider's XML Schema Definition like element, namespace, and type. Also we see AgileAuthenticator.xml has below java implementation definition. The definition clearly show us the implementation is AgileAuthenticationProviderImpl.class, so if we look at the class file and check what type of Login class wrapped in AppConfigurationEntry to be sent to Java Authentication and Authorization Service (JAAS), we get the concrete login class, that is "WLSLoginModule" private AppConfigurationEntry getConfiguration(HashMap paramHashMap) { paramHashMap.put("database", this.database); return new AppConfigurationEntry("com.agile.admin.security.weblogic.WLSLoginModule", this.controlFlag, paramHashMap); } JAAS then transfers the authentication to WLSLoginModule.class to manage. The module then check the superadmin user account (need to decrypt the username and password first if Agile is 9.3.2 and 9.3.3) against the database. DB Connection Many people are confused why Agile defines db connection parameters in two places. One is in agile.properties and the other one is in which is defined in CP-AgileContentPool-jdbc.xml as a Connection Pool. agile.properties definition CP-AgileContentPool-jdbc.xml definition This is a correct design, not a redundance. During superadmin authentication, many Weblogic components are not initialized that connection pool is not ready. So WLSLoginModule cannot get a connection from the AgileContentPool. In this case, WLSLoginModule set up a direct jdbc connection to remote database with parameters from agile.properties, we call it a LocalConnection, which uses the traditional register function listed below. Class.forName("oracle.jdbc.driver.OracleDriver");

In Agile PLM, we all know superadmin account is used to start Weblogic server, this account is authenticated against Agile database. In previous version before 9.3.2, we define the username and its...

Login/Logout impacted by USER_USAGE_HISTORY

You may see this type of error "The Session Has Been Terminated. Please Login Again" from Agile WebClient when you try to login. There are many possible root causes. This blog article discusses one of them, which happens on Weblogic, and presents the troubleshooting steps. READ THROUGH LOG From stdout.log, we get "Session terminated" error which shows one user is trying to login, but immediately he is forced to logout. Just from this error, we cannot determine why his login is terminated. [PCMHelperSessionBean_9xz6y2_Impl:ERROR] Session terminated... java.lang.NullPointerExceptionat com.agile.util.sql.LocalConnectionFactory.getJDBCConnection(LocalConnectionFactory.java:82)...at com.agile.ipa.pc.CMHelper.logout(CMHelper.java:110)...at com.agile.ui.pcm.login.LoginHandler.forwardToForLoginError(LoginHandler.java:427)at com.agile.ui.pcm.login.LoginHandler.login(LoginHandler.java:269) Now read another stderr.log, we can get a clearer clue that during password authentication with database, JDBC driver fails to get one free connection from pool. Perhaps the connection pool is exhausted and cannot allocate more in promp manner. java.lang.NullPointerException at com.agile.util.sql.LocalConnectionFactory.getJDBCConnection(LocalConnectionFactory.java:82) at com.agile.util.sql.ConnectionFactory.getConnection(ConnectionFactory.java:37) at com.agile.admin.security.userregistry.DBUserAdapter.getConnection(DBUserAdapter.java:203) at com.agile.admin.security.userregistry.DBUserAdapter.checkPassword(DBUserAdapter.java:56) at com.agile.admin.security.weblogic.WLSLoginModule.validate(WLSLoginModule.java:375) at com.agile.admin.security.weblogic.WLSLoginModule.login(WLSLoginModule.java:166) Read more log we find a "read timeout" error thrown from JDBC driver when a user tries to logout manually. Here 1200 seconds is defined by a Session EJB PCMHelperSessionBean. From this error trace, we know that when user logs out, Agile will save his logout information into User Usage Report related table for audit, but now this user appears fail to logout. Cookie: JSESSIONID=GNvkTNMJGvP10swTHYFxQSphymcR1rWHtyTnrJqr2J8J1rlbKHr4!284310265!1024205145; j_password=xxx; j_username=4839C73859281CD3; ]", which is more than the configured time (StuckThreadMaxTime) of "1,200" seconds. Stack trace:java.net.SocketInputStream.socketRead0(Native Method)...oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1062)com.agile.report.server.usage.UserUsageHandler.logout(UserUsageHandler.java:160)...com.agile.pc.cmserver.pcmhelper.PCMHelperSessionBean.logoutUser(PCMHelperSessionBean.java:336)...com.agile.ui.pcm.login.LoginHandler.logout(LoginHandler.java:1713) We get the user id from j_username, and get confirmation this user cannot logout and his browser shows an hourglass forever. Then we get to know during logout, the data cannot immediately write into user's usage report table. As of now, we need to focus on the database performance problem, we may ask for a RDA report for analysis. But we have another way to research more. ANALYZE DATABASE We know all the login and logout data are saved in user's usage report table which is user_usage_history. First we check if any index is lost. select index_name, index_type, table_owner, uniqueness, status from user_indexes where table_name='USER_USAGE_HISTORY';INDEX_NAME INDEX_TYPE TABLE_OWNER UNIQUENESS STATUS ------------------------------ --------------------------- ------------------------------ ---------- --------LOGOUT_TIME_IDX FUNCTION-BASED NORMAL AGILE NONUNIQUE VALID USER_UHIS_IDX1 NORMAL AGILE NONUNIQUE VALID Now we see the unique index USER_USAGE_HIS_PK disappears. Second, we check how many data in this table. select count(*) from user_usage_history; 72654337 Missed Unique index and huge data in table, that is the real problem. SOLUTION Re-build the unique index.CREATE UNIQUE INDEX USER_USAGE_HIS_PK on USER_USAGE_HISTORY (SID); Purge too old data from user_usage_history table. Due to the table is huge, we cannot use Delete DML to perform, so we have a workaround.-- preserve useful data create table user_usage_history_bk as select * from user_usage_history where login_time>to_date('2014-01-01', 'YYYY-MM-DD');-- truncatetruncate table user_usage_history;-- disable triggeralter trigger user_usage_history_t disable;-- retrieve useful datainsert into user_usage_history select * from user_usage_history_bk;commit;-- enable triggeralter trigger user_usage_history_t enable;-- analyze statisticsexec dbms_stats.gather_table_stats('agile','USER_USAGE_HISTORY');

You may see this type of error "The Session Has Been Terminated. Please Login Again" from Agile WebClient when you try to login. There are many possible root causes. This blog article discusses one of...

Use mail.debug to collect JavaMail log for Agile Email Notification

You may always have email problem from Agile that users are able to get Inbox mail on Agile UI, but fail to get it through email, it happens randomly, sometime users receive and sometime not. And you see error "Actual Exception : null" from server log like below. Actual Exception : nullat com.agile.common.server.notification.SendEmail.send(SendEmail.java:307)at com.agile.common.server.notification.SendEmailListener.onMessage(SendEmailListener.java:43)at weblogic.ejb.container.internal.MDListener.execute(MDListener.java:585)at weblogic.ejb.container.internal.MDListener.transactionalOnMessage(MDListener.java:488)at weblogic.ejb.container.internal.MDListener.onMessage(MDListener.java:385)at weblogic.jms.client.JMSSession.onMessage(JMSSession.java:4659)at weblogic.jms.client.JMSSession.execute(JMSSession.java:4345)at weblogic.jms.client.JMSSession.executeMessage(JMSSession.java:3821)What does above email error mean? Nothing, absolutely nothing that makes sense. Then you may consult Oracle Agile Support to diagnose. You get instructions: Enable Email Notification Debug for com.agile.pc.cmserver.notification and com.agile.common.server.notification Use Telnet to verify the functionality of SMTP It most of cases they are helpful and help you quickly find out the cause. But in complicated case that happens randomly, they will not. JavaMail One year ago I wrote a Document article in My Oracle Support about how to make Agile email to SMTP at non-default port 25.Setup SMTP Server Port To A Value Other Than The Default 25, To Receive Email Notifications From Agile Server (Doc ID 1468816.1) Then if you think more, you may ask what other options we can set to JVM parameters to extend JavaMail's function. Agile invokes the simplest function (Transport.send) of JavaMail to deliver the email entry to SMTP without Authentication (Relay is used), so definitely you can use these options listed in this link.https://javamail.java.net/nonav/docs/api/ mail.debugWhat I introduce here is to use mail.debug option to enable the debugging of email, including the handshake, authentication, delivery and disconnection. DEBUG: JavaMail version 1.4.1DEBUG: not loading file: /usr/java/jdk1.7.0_21/jre/lib/javamail.providersDEBUG: java.io.FileNotFoundException: /usr/java/jdk1.7.0_21/jre/lib/javamail.providers (No such file or directory)DEBUG: !anyLoadedDEBUG: not loading resource: /META-INF/javamail.providersDEBUG: successfully loaded resource: /META-INF/javamail.default.providersDEBUG: Tables of loaded providersDEBUG: Providers Listed By Class Name: {com.sun.mail.smtp.SMTPSSLTransport=javax.mail.Provider[TRANSPORT,smtps,com.sun.mail.smtp.SMTPSSLTransport,Sun Microsystems, Inc], com.sun.mail.smtp.SMTPTransport=javax.mail.Provider[TRANSPORT,smtp,com.sun.mail.smtp.SMTPTransport,Sun Microsystems, Inc], com.sun.mail.imap.IMAPSSLStore=javax.mail.Provider[STORE,imaps,com.sun.mail.imap.IMAPSSLStore,Sun Microsystems, Inc], com.sun.mail.pop3.POP3SSLStore=javax.mail.Provider[STORE,pop3s,com.sun.mail.pop3.POP3SSLStore,Sun Microsystems, Inc], com.sun.mail.imap.IMAPStore=javax.mail.Provider[STORE,imap,com.sun.mail.imap.IMAPStore,Sun Microsystems, Inc], com.sun.mail.pop3.POP3Store=javax.mail.Provider[STORE,pop3,com.sun.mail.pop3.POP3Store,Sun Microsystems, Inc]}DEBUG: Providers Listed By Protocol: {imaps=javax.mail.Provider[STORE,imaps,com.sun.mail.imap.IMAPSSLStore,Sun Microsystems, Inc], imap=javax.mail.Provider[STORE,imap,com.sun.mail.imap.IMAPStore,Sun Microsystems, Inc], smtps=javax.mail.Provider[TRANSPORT,smtps,com.sun.mail.smtp.SMTPSSLTransport,Sun Microsystems, Inc], pop3=javax.mail.Provider[STORE,pop3,com.sun.mail.pop3.POP3Store,Sun Microsystems, Inc], pop3s=javax.mail.Provider[STORE,pop3s,com.sun.mail.pop3.POP3SSLStore,Sun Microsystems, Inc], smtp=javax.mail.Provider[TRANSPORT,smtp,com.sun.mail.smtp.SMTPTransport,Sun Microsystems, Inc]}DEBUG: successfully loaded resource: /META-INF/javamail.default.address.mapDEBUG: !anyLoadedDEBUG: not loading resource: /META-INF/javamail.address.mapDEBUG: not loading file: /usr/java/jdk1.7.0_21/jre/lib/javamail.address.mapDEBUG: java.io.FileNotFoundException: /usr/java/jdk1.7.0_21/jre/lib/javamail.address.map (No such file or directory) When JavaMail is first time referenced, it will initialize all the default configuration from JRE, like you see from above log. It has nothing to do with Agile so let's leave it alone. Then JavaMail will try to connect to remote SMTP at specified port 25. Default authentication is false. DEBUG: getProvider() returning javax.mail.Provider[TRANSPORT,smtp,com.sun.mail.smtp.SMTPTransport,Sun Microsystems, Inc]DEBUG SMTP: useEhlo true, useAuth falseDEBUG SMTP: trying to connect to host "dummy-smtp-server.oracle.com", port 25, isSSL false220-dummy-smtp-server-proxy.oracle.com ESMTP Oracle Corporation - Unauthorized Use Prohibited 220 Ready at Thu, 30 Jan 2014 03:42:32 GMTDEBUG SMTP: connected to host "dummy-smtp-server.oracle.com", port: 25 Next, JavaMail simulates the TELNET method to handshake with SMTP server. It verifies the sender and recipient if they are allowed to relay (internal user sends to internal user, internal user sends to external user, or external user sends to internal user). EHLO agileServerhost250-dummy-smtp-server-proxy.oracle.com Hello agileServerhost.oracle.com [1xx.1xx.1xx.1xx], pleased to meet you250-ENHANCEDSTATUSCODES250-PIPELINING250-8BITMIME250-SIZE 14680064250-STARTTLS250-DELIVERBY250 HELPDEBUG SMTP: Found extension "ENHANCEDSTATUSCODES", arg ""DEBUG SMTP: Found extension "PIPELINING", arg ""DEBUG SMTP: Found extension "8BITMIME", arg ""DEBUG SMTP: Found extension "SIZE", arg "14680064"DEBUG SMTP: Found extension "STARTTLS", arg ""DEBUG SMTP: Found extension "DELIVERBY", arg ""DEBUG SMTP: Found extension "HELP", arg ""DEBUG SMTP: use8bit falseMAIL FROM:250 2.1.0 ... Sender okRCPT TO:250 2.1.5 ... Recipient okDEBUG SMTP: Verified AddressesDEBUG SMTP: "jie, chen (chenjie)" After that, it deliver the email message entry. DATA354 Enter mail, end with "." on a line by itselfDate: Thu, 30 Jan 2014 03:41:53 +0000 (GMT)From: test@oracle.comReply-To: test@oracle.comTo: "jie, chen (chenjie)" Message-ID: <395344347.0.1391053313086.JavaMail.oracle@slag9320w5c>Subject: Administrator (admin) has sent admin to youMIME-Version: 1.0Content-Type: text/plain ; charset="UTF-8"Content-Transfer-Encoding: 7bitadmin has been sent to you by Administrator (admin).Comments from Administrator (admin):test 123Access the following URL to see admin:http://agileServerhost.oracle.com:80/Agile/PLMServlet?action=OpenEmailObject&classid=11605&objid=704Sent by: Administrator (admin).250 2.0.0 s0U3gWNJ015603 Message accepted for delivery And finally quit. QUIT221 2.0.0 dummy-smtp-server-proxy.oracle.com closing connection Scenario Below is two sample error detected by JavaMail DEBUG. javax.mail.SendFailedException: Invalid Addresses;nested exception is:class javax.mail.SendFailedException: 550 5.7.1 Unable to relay 454 4.7.0 Temporary authorization failure221 2.0.0 Service closing transmission channelDEBUG SMTP: QUIT failed with 454

You may always have email problem from Agile that users are able to get Inbox mail on Agile UI, but fail to get it through email, it happens randomly, sometime users receive and sometime not. And...

ORA-06502 error from AGILE.AGILE_SERVER_BOM_EXPANSION

Agile has a smart rule named "BOM Multi-Level Recursion". And when we route a Change Order to next status, Agile will check current Change Affected Items' Recursion status in Audit function. Suppose we have Part PART-123456789-01, which contains a BOM component PART-123456789-02, while PART-123456789-01 has its own BOM component PART-123456789-03. Now we create one Change Order against PART-123456789-03 and redline PART-123456789-03 to add PART-123456789-01, click Next Status, Audit function will detect below error. PART-123456789-03 has recursive BOM: PART-123456789-01 --> PART-123456789-02 --> PART-123456789-03 --> PART-123456789-01 That is the expected correct behavior. Last week I have one customer reporting that he gets an unfriendly error message when do Audit Release function in Agile 9.3.0.2. 14/01/20 03:00:08 java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error: character string buffer too smallORA-06512: at "AGILE.AGILE_SERVER_BOM_EXPANSION", line 711ORA-06512: at line 1 Scenario Let's discuss why the error occurs un such matter. Here I directly give out the scenario of problem. My Item numbers are very longer than 32 characters like below with BOM component structure. PART-123456789-ABCDEFGHIJKLMNOPQRSTUVWXYZ-01 |_ PART-123456789-ABCDEFGHIJKLMNOPQRSTUVWXYZ-02 |_ PART-123456789-ABCDEFGHIJKLMNOPQRSTUVWXYZ-03 Then I redline PART-123456789-ABCDEFGHIJKLMNOPQRSTUVWXYZ-03 to add PART-123456789-ABCDEFGHIJKLMNOPQRSTUVWXYZ-01, click Audit Release or route to next status, I see exactly same problem. Analysis Below is the server log, but it has not much help to us. We can only look at the package AGILE.AGILE_SERVER_BOM_EXPANSION at line 711 and around. 14/01/20 03:00:08 java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error: character string buffer too smallORA-06512: at "AGILE.AGILE_SERVER_BOM_EXPANSION", line 711ORA-06512: at line 114/01/20 03:00:08 at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:138)14/01/20 03:00:08 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:316)14/01/20 03:00:08 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:282)14/01/20 03:00:08 at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:639)14/01/20 03:00:08 at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:184)14/01/20 03:00:08 at oracle.jdbc.driver.T4CCallableStatement.execute_for_rows(T4CCallableStatement.java:873)14/01/20 03:00:08 at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1161)14/01/20 03:00:08 at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3001)14/01/20 03:00:08 at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3093)14/01/20 03:00:08 at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4286)14/01/20 03:00:08 at com.agile.pc.cmserver.item.ItemBOMExpansion.getRowBomCycleDetails(ItemBOMExpansion.java:265)14/01/20 03:00:08 at com.agile.pc.cmserver.item.ItemBOMExpansion.getAllBomCycleRows(ItemBOMExpansion.java:178)14/01/20 03:00:08 at com.agile.pc.cmserver.change.ChangeService.bomRecursionCheck(ChangeService.java:4287)14/01/20 03:00:08 at com.agile.pc.cmserver.base.BaseServiceRoute.checkReq(BaseServiceRoute.java:3356)14/01/20 03:00:08 at com.agile.pc.cmserver.base.BaseServiceRoute.moveStatusForward(BaseServiceRoute.java:3063)14/01/20 03:00:08 at com.agile.pc.cmserver.base.BaseServiceRoute.doAuditStatus(BaseServiceRoute.java:1938)14/01/20 03:00:08 at com.agile.pc.cmserver.base.BaseServiceRoute.doAuditCurrentStatus(BaseServiceRoute.java:1496)14/01/20 03:00:08 at com.agile.pc.cmserver.base.BaseServiceRoute.auditCurrentStatus(BaseServiceRoute.java:1467)14/01/20 03:00:08 at com.agile.pc.cmserver.base.CMRouteSessionBean.preAuditStatusBatch(CMRouteSessionBean.java:1549) Package find_bom_recursion: PROCEDURE find_bom_recursion( ... o_itemNumbers OUT t_Varchar2s, select item_number, class, subclass into itm_number, itm_class, itm_subclass from item where id = itm_id; o_itemNumbers(compCursor) := itm_number; -- <--- line 711 From above, we understand o_itemNumbers is Agile customized type t_Varchar2s, and at line 711 one array of it is assigned with itm_number column data which comes from item table. And we can find the definition of t_Varchar2s from in ORA_HOME/admin/AGILEINSTANCE/create/AGILESCHEMA/useragile.sqlt_Varchar2s is defined in sys schema with sysdba role, and it is referenced as synonyms in AGILE schema. declare type_exists number; sql_command varchar2(200);begin select count(1) into type_exists from user_objects where object_name = 'T_VARCHAR2S' and object_type='TYPE'; if (type_exists = 0) then sql_command := 'create type t_varchar2s as table of varchar2(32)'; execute immediate sql_command; end if;end; So definitely, t_varchar2s is a collection type of varchar2 and each element only alows 32 characters at most. The last As a conclusion of this BUG, you can use Note 1471579.1 to detect and remove all recursive BOMs, or wait for Oracle to fix this BUG officially. I had no chance to verify other version of Agile like 9.3.2 or 9.3.3, I believe it happens same there. Maybe I am wrong, but please go ahead to verify them if you are interested in this.

Agile has a smart rule named "BOM Multi-Level Recursion". And when we route a Change Order to next status, Agile will check current Change Affected Items' Recursion status in Audit function....

Conflict between Change Control and ASL Mapping

Yesterday I got one strange report that on Agile 9.3.1.2, adding a Supplier into Item's Supplier tab will always remove all the data from Item.PageTwo.MultiList01 field which is assigned to a User Group list.The detailed problem description is like below.In JavaClient, MultiList01 attribute on Parts class's PageTwo tab is enabled and assigned with User Group list. On WebClient, user created a new Part and assign MultiList01 with two UserGroups: "Global User Group Test1" and "Personal Group_Test1". Then go to Suppliers tab to add three Suppliers. Switch back to Part's TitleBlock, will see MultiList01 loses the User Group data. To confirm if MultiList01 really loses the data or it saves with other wrong data, I need to check the database and find strange data that MultiList01 saves wrong data ",7976911,7976907,7976959,", which are exactly the ID of these three Suppliers. Then I can suspect the Supplier attribute on Suppliers tab must be mapped to MultiList01. However when I check Supplier in JavaClient, the "ASL mapped to" is blank. More interesting thing is the database clearly shows Supplier attribute (Base ID =2000004219) is mapped to 2090, which is PageTwo.MultiList01 Base ID. Till now, we can get a conclusion that Supplier data is really mapped to MultiList01, though we assign MultiList01 to User Group list and Supplier does not set "ASL mapped to". It must be another function which overrides "ASL mapped to" visibility in JavaClient with high priority.That is the "Change Controlled" function. We immediately see "ASL mapped to" with value "MultiList01" when we disable Change Controlled for Multilist01 If one attribute is Change Controlled, Supplier data cannot be mapped to this attribute theoretically because Supplier could be dynamically modified by users, not by Changes. In real situation of Agile 9.3.1.2, it could be a Code Defect.We can imagine the scenario customer met. He setup Parts.PageTwo.Multilist01 assigned with Supplier list, then in Parts.Suppliers.Supplier attribute, he set "ASL mapped to" to "Multilist01". Later company business is changed, so he set Multilist01 with Change Controlled and re-assign with User Group list. He forgot to remove "ASL mapped to" before he did modifications to Multilist01.Finally we know the solution, it depends on real business.If still need to mapping Supplier to Parts.PageTwo attribute, should modify "ASL mapped to" to other one attribute which already has assigned with Suppliers list.If do not need "ASL mapped to" function, should delete the data from database level. We cannot do it from JavaClient UI.delete propertytable where id in (select p.id from propertytable p, nodetable n where p.parentid =n.id and n.inherit=2000004219 and propertyid=794)

Yesterday I got one strange report that on Agile 9.3.1.2, adding a Supplier into Item's Supplier tab will always remove all the data from Item.PageTwo.MultiList01 field which is assigned to a...

PL/SQL to delete invalid data from token Strings

Previous article describes how to delete the duplicated values from token string in bulk mode. This one extends it and shows the way to delete invalid data. Scenario Support we have page_two and manufacturers tables in database and the table DDL is: SQL> desc page_two; Name NULL? TYPE ----------------------------------------- -------- ------------------------ MULTILIST04 VARCHAR2(765)SQL>SQL> desc manufacturers; Name NULL? TYPE ----------------------------------------- -------- ------ ID NOT NULL NUMBER NAME VARCHAR In table page_two, column multilist04 stores a token string splitted with common. Each token represent a valid ID in manufacturers table. My expectation is to delete invalid token strings from page_two.multilist04, which have no mapping id in manufacturers.id. For example in below SQL result: ,6295728,33,6295729,6295730,6295731,22, , value 33 and 22 are invalid data because there is no ID equals to 33 or 22 in manufacturers table. So I need to delete 33 and 22. SQL> col rowid format a20;SQL> col multilist04 format a50;SQL> select rowid, multilist04 from page_two;ROWID MULTILIST04-------------------- --------------------------------------------------AAB+UrADfAAAAhUAAI ,6295728,6295729,6295730,6295731,AAB+UrADfAAAAhUAAJ ,1111,6295728,6295729,6295730,6295731,AAB+UrADfAAAAhUAAK ,6295728,111,6295729,6295730,6295731,AAB+UrADfAAAAhUAAL ,6295728,6295729,6295730,6295731,22,AAB+UrADfAAAAhUAAM ,6295728,33,6295729,6295730,6295731,22,SQL> select id, encode_name from manufacturers where id in (1111,11,22,33);No rows selectedSQL> Solution As there is no existing SPLIT function or related in PL/SQL, I should program it by myself. I code Split intermediate function which is used to get the token value between current splitter and next splitter. Next program is main entry point, it get each column value from page_two.multilist04, process each row based on cursor. When it get each multilist04 value, it uses above Split function to get each token string stored to singValue variant, then check if it exists in manufacturers.id. If not found, set fixFlag to 1, pending to be deleted.

Previous article describes how to delete the duplicated values from token string in bulk mode. This one extends it and shows the way to delete invalid data. Scenario Support we have page_two and...

Remove duplicated values programmatically

We may always see multiple duplicated values for one attribute on Agile WebClient, which happens sometimes, very often. It is OK to correct them from UI if less objects have such issue. But it is boring if too many exist there.We will have a very simple way to quickly identify the column in database table and fix them effectively by a PL/SQL Procedure. Suppose we have one Part object TESTPART001 which has multiple values for "CM Access" attribute like below. We need to figure out this attribute's table and column name in database. Since this "CM Access" attribute is from Page Two page, so we go to Part's subclass Page Two tab in JavaClient. We get "PAGE_TWO.MULTILIST02". That is to say, the attribute value is saved in Page_two table, multilist02 column. To get which row in page_two table, next we shall get this TESTPART001's ID first. There is a very quick way: put your mouse pointer on any tab of that part, you will get a hint of Javascript in browser's status bar. In this example, this TESTPART001's ID is 967250145. To confirm this, we verify it against ITEM table, and it is. Then we can query page_two table with the ID to get multilist02 column's value. You will find multiple duplicated values reside. Then I coded a SQL procedure to fix it programmatically. You can revise it to put into your own scenario. To remove the duplicated value: update page_two set multilist02 = remove_dup_vals(multilist02) where id=967250145;commit;

We may always see multiple duplicated values for one attribute on Agile WebClient, which happens sometimes, very often. It is OK to correct them from UI if less objects have such issue. But it...

JK Connector for Tomcat on IIS 7.5

There are many ways to setup proxy server for Agile File Server. Apache HTTP Web Server, IIS or hardware Load Balancer. If you already have IIS as Proxy for Agile Application Server, then you are fine to install JK Connector on IIS to support Agile File Server, no need to setup other Proxy servers. This document will show how to configure JK Connector on IIS as proxy to service backend Agile File Server. It is also a practical instruction for non-Agile users on how to setup JK Connector 1.2.37 for Tomcat 7.x on IIS 7.5 (Windows 2008R2). Platform We have below platform information. OS: Windows 2008 R2 64 bitIIS: 7.5Apache Tomcat Version 7.0.26Tomcat Connector: 1.2.37 We expect the backend Tomcat server to be http://tomcat.internal.com:8080/Filemgr/ , external users to access it from http://server.company.com/Filemgr/ . JK Connector Properties First we create workers.properties file in the JK Connector directory. ## workers.propertiesworker.list=ajp13,wlb,jkstatusworker.ajp13w.type=ajp13worker.ajp13w.host=slag9320w8-4.sl.agilesoft.comworker.ajp13w.port=8009worker.wlb.type=lbworker.wlb.balance_workers=ajp13wworker.jkstatus.type=status And create uriworkermap.properties. In this file, we input the App Context here like Filemgr, webdav. Be sure /jkmanager is present ## uriworkermap.properties/Filemgr/*=wlb/webdav/*=wlb/webdav=wlb/jkmanager=jkstatus Create the third file isapi_redirect.properties in same directory ## isapi_redirect.propertiesextension_uri=/jakarta/isapi_redirect.dllworker_file=C:\IISProxy\Connector\workers.propertiesworker_mount_file=C:\IISProxy\Connector\uriworkermap.propertieslog_file=C:\IISProxy\Connector\isapi_redirect_out.loglog_level=trace For research purpose, I set log_level to be "trace" . You can set to "error" to supress too much trace data recorded in this isapi_redirect_out.log file. Now go back to remote Tomcat machine, make sure AJP/1.3 is present in server.xml file, and the port number is exactly same as the one in workers.properties. IIS Configuration 1. Below we need to manually configure on IIS. First we go to the Server in IIS, go to "ISAPI and CGI Restrictions", add "ISAPI or CGI path". Check "Allow extension path to execute" checkbox. 2. Go to Web Site, in "ISAPI Filters", add a new "ISAPI Filter" 3. In the same Web Site, create a Virtual Directory, make sure Alias is lowercase. 4. Go to "Handler Mappings", click link of "Edit Feature Permissions", select all of "Read", "Script" and "Execute" checkboxes. 5. Exit IIS Manager, restart Control Panel || Services || "World Wide Web Publishing Service", then open IIS Manager again, restart Server and Web Site 6. Make sure you can access both below links from browser. http://tomcat.internal.com:8080/Filemgr/Configuration http://server.company.com/Filemgr/Configuration Troubleshooting 1. If the Proxy server does not work, and no isapi_redirect_out.log generated, then issue below command to see if the DLL is loaded. If not, you may have re-configure above steps from scratch. C:\>tasklist /m isapi*Image Name PID Modules========================= ======== =============================w3wp.exe 4920 isapi.dll, isapi_redirect.dll 2. If isapi_redirect_out.log is generated, you can research it, find any errors and fix it, yourself, by using Google.

There are many ways to setup proxy server for Agile File Server. Apache HTTP Web Server, IIS or hardware Load Balancer. If you already have IIS as Proxy for Agile Application Server, then you are fine...

Custom Cookie impacts Agile File Server operations

Many customers use Web Proxy or hardware Load Balancer in front of Agile Application Server to implement the function of proxy and failover. LoadBalancer may be configured to insert its own cookies to keep session stickiness across different back end Application Servers. It is for technical purpose.While Web Proxy also may be added customized cookie for business uses for customers' own. Each of case about custom cookie may cause File Server fail to upload/download files on IE browser. Let's see how it happens and how to resolve. Simulation I have no hardware Load Balancer, to simulate the problem I just use Apache HTTP Server 2.2. First I add several custom cookies to Apache HTTP. Below is my input in httpd.conf. You may wonder why I add them into Apache HTTP. It is for customer specific that someone may need these customized cookies to develop custom SDK program. Header add Set-Cookie: Jie_Cookie_1=hellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookie;Header add Set-Cookie: Jie_Cookie_2=hellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookie;Header add Set-Cookie: Jie_Cookie_3=hellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookie;Header add Set-Cookie: Jie_Cookie_4=hellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookie;Header add Set-Cookie: Jie_Cookie_5=hellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookie;Header add Set-Cookie: Jie_Cookie_6=hellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookie; Then I config this Apache HTTP as Web Proxy to one Agile Application Server only. Now I open IE to login the Web Proxy and try to download a file attachment, nothing happens. Try to add attachment, IE browser says "Internet Explorer cannot display the webpage" and we notice that the URL is very longer than 2100 characters. http://xxxx.com:8080/Filemgr/AttachmentServlet?ssoToken=%7BAES%3A128%7D1B881B176946D1C28FB5BADF946C04EA72A46F977A78A3DBAED4CF.... I find there is no any error message in both App and FileServer log. However when I switch to Firefox and Chrome, everything work fine even with the long URL. So definitely it is IE specific and it brings us to a known IE issue when it handles long URL. http://support.microsoft.com/kb/208427 But if we remove above cookies from httpd.conf, the URL of AttachmentServlet will much shorter. Why Below attachment is cookie section collected from HTTP header when do request to Agile Application Server. Agile persists them on App server forever. When user uploads/downloads files, Agile will get all these cookies and their values, then encrypt them, encode them, again append them as a part of URL to AttachmentServlet?ssoToken, in order to ask FileServer to retrieve the session data on itself side to get authenticated. So more cookies are included, longer AttachmentServlet URL is. Once the length is longer than 2083, IE then fail to handle. From below DEBUG log (DEBUG for com.agile.webfs in agile.properties), we can see how these cookies are included by Agile. <2013-08-20 05:49:40,111>get agile.properties<2013-08-20 05:49:40,112>added cookie: CognosCookie, Value: e0FFUzoxMjh9QTU3RkFFMjg4M0Y5NTBENjQ0QzZGNUE0NTM3NjAyRjEyQTEx<2013-08-20 05:49:40,113>added cookie: Jie_Cookie_1, Value: hellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookie<2013-08-20 05:49:40,113>added cookie: Jie_Cookie_2, Value: hellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookie<2013-08-20 05:49:40,114>added cookie: Jie_Cookie_3, Value: hellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookie<2013-08-20 05:49:40,114>added cookie: Jie_Cookie_4, Value: hellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookie<2013-08-20 05:49:40,115>added cookie: Jie_Cookie_5, Value: hellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookie<2013-08-20 05:49:40,116>added cookie: Jie_Cookie_6, Value: hellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookiehellocookie<2013-08-20 05:49:40,116>added cookie: JSESSIONID, Value: FP4MSTlb0B1v1QJdmnSvhdmvm32Hnzz1MPpVDvpMtczwfLlSH3cr!-973579987<2013-08-20 05:49:40,117>added cookie: jsDebug, Value: 0<2013-08-20 05:49:40,117>added cookie: invalidate_session, Value: false<2013-08-20 05:49:40,117>added cookie: j_username, Value: e0FFUzoxMjh9QTU3RkFFMjg4M0Y5NTBENjQ0QzZGNUE0NTM3NjAyRjEyQTEx<2013-08-20 05:49:40,118>added cookie: j_password, Value: JSUle0FFUzoxMjh9RTY1RURCNDM3NjhBMUZCMzM4NEJGMzI3ODg2QjExMkM2ODNDN0RDN0U0NjlCMURDQzk2QkJEMTJBRjE0NDU4N0REM0U4RUZBNTkwRTg0RURDRDA2OUQ4NjEwNUYwNDI5RjNENDU4NkZCMDg2NzNCMDQwODYwQjU3MUEzRUNDNUE0NkRFJSUl Solution We can exclude these cookies in agile.properties at APP level (it will not impact Web Proxy and Load Balancer session stickiness behavior). So Agile will not include them again when to assemble ssoToken for AttachmentServlet. excluded.cookie.names=Jie_Cookie_1,Jie_Cookie_2,Jie_Cookie_3,Jie_Cookie_4,Jie_Cookie_5,Jie_Cookie_6 DEBUG log agains shows the custom cookies are excluded as expected. <2013-08-20 05:20:25,704>get agile.properties<2013-08-20 05:20:25,705>excluded cookie name in apcm: JIE_COOKIE_1, JIE_COOKIE_2, JIE_COOKIE_3, JIE_COOKIE_4, JIE_COOKIE_5, JIE_COOKIE_6<2013-08-20 05:20:25,706>added cookie: CognosCookie, Value: e0FFUzoxMjh9QkQ3M0JFNTEzRjA1M0YxNDhCRjYwMDBERkJEMTYyRUQwMTdD<2013-08-20 05:20:25,706>added cookie: JSESSIONID, Value: Xh5qSTpW2jtLhTCd8ThFmpWwf5Kh1XbPdSMWWNjgsWYmD9XzL8sr!683789495<2013-08-20 05:20:25,707>added cookie: invalidate_session, Value: false<2013-08-20 05:20:25,707>added cookie: j_username, Value: e0FFUzoxMjh9QkQ3M0JFNTEzRjA1M0YxNDhCRjYwMDBERkJEMTYyRUQwMTdD<2013-08-20 05:20:25,708>added cookie: j_password, Value: JSUle0FFUzoxMjh9NTZDRTJGMDUwNDFDMkUyM0YwRjk1MUZGQ0I0MTkzQzQxRTRBNERDNzA2NjlFNTc1NTMxNzdBRDdDNURBNjlCNEY5ODhCOTk5NjgwMjRBQTc3MUM4RTkzRDExMjhBMDU1MTg2RTU1ODk5QkM3NDRFQkEyRDk0Njc1RkFENjg3MjE3QjZBJSUl<2013-08-20 05:20:25,708>added cookie: jsDebug, Value: 0

Many customers use Web Proxy or hardware Load Balancer in front of Agile Application Server to implement the function of proxy and failover. LoadBalancer may be configured to insert its own cookies to...

Rotate stdout.log for Weblogic running as Windows Service mode

To rotate the stdout.log of Weblogic on Windows Service is quite easy to achieve. It is different from other server log and platforms for Weblogic. I have no idea why Weblogic design as that but it is really tricky. We will see how it setups and how it works for Agile PLM from below presentation. How to setup When we install Agile PLM service against Weblogic for Windows, we use "beasvc -install -svcname:AgilePLM" command in installService.cmd. So we need to modify the CMD file. First, we need to remove "-Dweblogic.Stdout=%STDOUT% -Dweblogic.Stderr=%STDERR%" Then we add -log:%STDOUT% to the end of command line "beasvc -install -svcname:AgilePLM". If to put it together we have: set STDOUT="C:\Agile\Agile931/agileDomain/servers/SLAG9311W8-1-AgileServer/logs/stdout.log"set CMDLINE="-server -XX:MaxPermSize=256M -ms1280M -mx1280M -XX:NewSize=256M -XX:MaxNewSize=256M -classpath \"%CLASSPATH%\" %JMX_SET% -Dweblogic.Name=SLAG9311W8-1-AgileServer \"-Dbea.home=C:\bea\" -Dweblogic.management.username=%WLS_USERNAME% -Dweblogic.management.password=%WLS_PW% -Dweblogic.ProductionModeEnabled=%STARTMODE% \"-Djava.security.policy==C:\bea\wlserver_10.3/server/lib/weblogic.policy\" -Dagile.log.dir=C:\Agile\Agile931/agileDomain/servers/SLAG9311W8-1-AgileServer/logs -Dlog4j.configuration=file:.\config\log.xml weblogic.Server"rem *** Install the service"%WLS_HOME%\server\bin\beasvc" -install -svcname:"AgilePLM" -javahome:"%JAVA_HOME%" -execdir:"%INSTALL_DIR%" -extrapath:"%WLS_HOME%\server\bin;%JAVA_HOME%\bin" -cmdline:%CMDLINE% -password:%WLS_PW% -log:%STDOUT% Since we expect the stdout to file "C:\Agile\Agile931/agileDomain/servers/SLAG9311W8-1-AgileServer/logs/stdout.log", so we need to edit it (if it does not exist, must create it manually), add below lines in the head of stdout.log file. Attention, we add 5 lines in the stdout.log, and must press Enter/Carriage Return after the last line "#" ## ROTATION_TYPE = SIZE# SIZE_KB = 100# SIZE_TRIGGER_INTERVAL_MINS = 3# Then we re-install the Agile PLM service, you will see stdout.log will be rotated each 3 minutes if the filesize is larger than 100K. How it works When AgilePLM service runs, Weblogic check if the filesize is larger than defined size (100K in this sample). If it is, it will immediately rotate to new stdout log file with timestamp as the filename. During runtime, weblogic will check every 3 minutes as interval, if it is again larger than 100K, it will rotate to a second stdout log file, and so on. Unlike other log rotation, the stdout.log rotation for Windows Service has different filesize for each stdout log file. From below screenshot, we see each stdout.log-xxxxxxxxx has different size. That is to say, suppose we have current stdout.log filesize 420K, it does NOT rotate to 4 log files (420/100=4). Actually it only rotate to a single file like stdout.log-2013_08_06-05_44_05, then create a blank stdout.log replicated with only below content. That is to say, each stdout.log-xxxxxx has exactly same file header like below. ## ROTATION_TYPE = SIZE# SIZE_KB = 100# SIZE_TRIGGER_INTERVAL_MINS = 3#

To rotate the stdout.log of Weblogic on Windows Service is quite easy to achieve. It is different from other server log and platforms for Weblogic. I have no idea why Weblogic design as that but it is...

Transaction Timeout in Agile + Weblogic

We may see different transaction timeout error in Agile (It also happens to other applications). There are two places where control the Timeout setting, JTA, EJB tier and Loadbalancer/Proxy. I will demonstrate three cases to show how to identify them. Though most of the timeout issues are caused by applications and we MUST find out the REAL root cause to fix them definitely, increase transaction-timeout still can resolve most of these cases as a temporary solution/workaround. JTA Timeout JTA Timeout is the system-wide setting for the weblogic domain, in Agile it is agileDomain. That is to say, all the transaction are controlled by JTA globally in the same domain. In Agile, all of database access timeout are detected by JTA setting. For example: java.sql.SQLException: Transaction BEA1-03D472459EE07C52BE9E not active anymore. tx status = Marked rollback. [Reason=weblogic.transaction.internal.AppSetRollbackOnlyException]at weblogic.jdbc.jts.Driver.getTransaction(Driver.java:550)at weblogic.jdbc.jts.Driver.connect(Driver.java:112)at weblogic.jdbc.common.internal.RmiDataSource.getConnection(RmiDataSource.java:355)at com.agile.util.sql.DefaultConnectionFactory.getConnFromDS(DefaultConnectionFactory.java:84)at com.agile.util.sql.DefaultConnectionFactory.getJDBCConnection(DefaultConnectionFactory.java:48)at com.agile.util.sql.ConnectionFactory.getConnection(ConnectionFactory.java:37)at com.agile.util.sql.DebugConnectionFactory.getJDBCConnection(DebugConnectionFactory.java:59)at com.agile.util.sql.ConnectionFactory.getConnection(ConnectionFactory.java:37)Orjava.sql.SQLException: Transaction BEA1-14D07D02B16929FA01BC not active anymore. tx status = Rolled back. [Reason=weblogic.transaction.internal.TimedOutException: Transaction timed out after 7199 seconds BEA1-14D07D02B16929FA01BC]at weblogic.jdbc.jts.Driver.getTransaction(Driver.java:552)at weblogic.jdbc.jts.Driver.connect(Driver.java:112)at weblogic.jdbc.common.internal.RmiDataSource.getConnectionInternal(RmiDataSource.java:533)at weblogic.jdbc.common.internal.RmiDataSource.getConnection(RmiDataSource.java:498)at weblogic.jdbc.common.internal.RmiDataSource.getConnection(RmiDataSource.java:491)at com.agile.util.sql.DefaultConnectionFactory.getConnFromDS(DefaultConnectionFactory.java:84)at com.agile.util.sql.DefaultConnectionFactory.getJDBCConnection(DefaultConnectionFactory.java:48)at com.agile.util.sql.ConnectionFactory.getConnection(ConnectionFactory.java:37) You can set it in agileDomain - Services - JTA category. EJB Timeout In Agile, many EJB session beans MAY have their own transaction timeout settings which override the JTA system-wide timeout. They are defined in the weblogic-ejb-jar.xml. If you find the timeout value in error log does not match the one in JTA, then you may have to check the error trace carefully. Because it may be set elsewhere. For example below error shows the timeout error happens from "AdminSessionBean". java.rmi.RemoteException: Transaction Rolledback.; nested exception is: weblogic.transaction.internal.TimedOutException: Transaction timed out after 300 seconds BEA1-50CB4EE5D0A2611D0A36at weblogic.ejb.container.internal.EJBRuntimeUtils.throwRemoteException(EJBRuntimeUtils.java:103)at weblogic.ejb.container.internal.BaseRemoteObject.postInvoke1(BaseRemoteObject.java:591)at weblogic.ejb.container.internal.StatelessRemoteObject.postInvoke1(StatelessRemoteObject.java:60)at weblogic.ejb.container.internal.BaseRemoteObject.postInvokeTxRetry(BaseRemoteObject.java:441)at com.agile.admin.server.AdminSessionBean_yg79hz_EOImpl.invokeAction(AdminSessionBean_yg79hz_EOImpl.java:10294)at com.agile.ipa.pc.CMHelper.invokeCustomProcessActions(CMHelper.java:386)at com.agile.ui.pcm.common.ObjectViewHandler.invokeCustomProcessActions(ObjectViewHandler.java:8150)at sun.reflect.GeneratedMethodAccessor333.invoke(Unknown Source)at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)at java.lang.reflect.Method.invoke(Method.java:597)at com.agile.ui.web.action.ActionServlet.invokeMethod(ActionServlet.java:1067)We find AdminSession transaction is defined in applications.ear - admin.jar - META-INF directory Please consult Oracle Agile Support for similar EJB timeout errors before you modify them. WebTier Timeout Different Web tier has different timeout setting, for example below is from Apache HTTP timeout error. [Mon Jul 29 00:07:32 2013] [error] [client xxx.xxx.xxx.xxx] (OS 10060)A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond. : proxy: error reading status line from remote server xxxplm.company.com:80, referer: http://proxy.company.com/Agile/PLMServlet?module=LoginHandler&opcode=forwardToMainMenu[Mon Jul 29 00:07:32 2013] [error] [client xxx.xxx.xxx.xxx] proxy: Error reading from remote server returned by /Agile/PCMServlet, referer: http://proxy.company.com/Agile/PLMServlet?module=LoginHandler&opcode=forwardToMainMenu For this WebTier timeout error, you may refer to its vendor/document.

We may see different transaction timeout error in Agile (It also happens to other applications). There are two places where control the Timeout setting, JTA, EJB tier and Loadbalancer/Proxy. I...

Agile File Server impacted by TCP Compression Solution

Usually, Agile JavaClient is only used internally. Here "internally" I especially means the LAN in same physical location. I do not suggest it to be "externally" used across WAN or VPN. That is because JavaClient is a fat client, each EJB invockation involves too much redundant data in TCP packages. And you will see delay in JavaClient if you intend to use externally, the delay includes slow login, slow response of tab click. However there is still some customers using it externally and their feedback is JavaClient works very fast in WAN like in LAN, with some special network technology. Customers are happy with that, but issue comes... There is one typical case that Agile PLM and main File Server is deployed in Taiwan, the distributed File Server is deployed in Shanghai. All the users in Taiwan, Shanghai and Beijing (these three locations are physically far away) are using JavaClient, they do not like WebClient. Everything is OK except many Beijing users have issue to download files from Taiwan main File Server. Administrator sees below error in Tomcat log. AxisFault faultCode: {http://xml.apache.org/axis/}HTTP faultSubcode: faultString: (0)null faultActor: faultNode: faultDetail: {}:return code: 0{http://xml.apache.org/axis/}HttpErrorCode:0(0)nullat org.apache.axis.transport.http.HTTPSender.readFromSocket(HTTPSender.java:712)at org.apache.axis.transport.http.HTTPSender.invoke(HTTPSender.java:172)... ...at com.agile.webfs.components.fileserver.client.FileServerSoapBindingStub.ping(FileServerSoapBindingStub.java:449)at com.agile.webfs.client.IFSLocator.getRemoteFileServer(IFSLocator.java:127)at com.agile.webfs.client.IFSLocator.getConnection(IFSLocator.java:95) It happens randomly, and succeeds if Beijing users try more. We checked everything on Agile Server and File Server setting. When issue comes to me, the performance of JavaClient catches my attention. How could be JavaClient fast to connect remote Agile Server far away? After taking TCPDUMP we get some strange TCP data. First let's analyze the correct TCP data for a Success case on a File Server (not on client machine). Package 29860 shows the client 10.241.64.99 sends a POST to File Server 10.241.64.234, the File Server does not send back immediate response. Instead, it sends GET to Agile Server 10.241.64.60 and tries to get user authenticated in SSOAuthServlet. Agile Server sends back HTTP 200 and asks the File Server to re-authenticate in redirected j_security_check. After getting HTTP 200 and authentication at package 30000, File Server finally send back HTTP 200 to the original request from client 10.241.64.99 at package 30015, then at package 37900, client sends CheckOutServlet request and begins to download files at package 37936. If we look at the Stream Content between TCP Flow of package 29860, we see the Request and Response clearly. The Red words are Request from client and Blue words are response from File Server at package 30015. If we hide all other sessions between File Server and Agile Server, the client and File Server communication is quite simple like in below two screenshots. Now we go back to the Failure case and check the TCP on client machine (not on File Server machine). Below screenshot shows Beijing user client (10.17.11.93) sends a POST request to remote File Server 172.20.168.48 at package 8627. Then there is no feedback forever. Check the TCP Steam Content, no response in blue color. When we check the TCP on File Server 172.20.168.48, we see abnormal data. Package 93848 sends Reset and combines Acknowledge to client machine in order to close the TCP session. It is not a correct behavior. Then client sends Finish and Acknowledge as well. The Session is closed with Reset at wrong package 93850. I am confused by that until I read the TCP Stream Content of package 93847. It contains below: It gives me a supposition. The original package 93847 is inserted a wrong HTTP head Keep-Alive, which will make the http session alive in a given time period of Keep-Alive, and also it is not required for such a HTTP session as we see in a Success case, that is why the remote File Server sends back Reset to request to close the session. But where is "Keep-Alive" from? Connection: Keep-AliveX-RBT-Optimized-By: CQ-Riverbed (RiOS 7.0.6) IK Riverbed is a TCP Compression solution, especially for WAN to pack huge TCP package, compress and optimize it during transmission across faraway remote network. No wonder that customer has best performance for remote JavaClient like in local computing. In some cases it has a command "IK" in the HTTP head, it means "Insert-Keep-Alive" and automatically insert Keep-Alive field into HTTP head. Check Riverbed user guide. Now we find the root cause and if we exclude the File Server IP from Riverbed, all users in remote Beijing are able to download files without any error.

Usually, Agile JavaClient is only used internally. Here "internally" I especially means the LAN in same physical location. I do not suggest it to be "externally" used across WAN or VPN. That is...

Agile 9.3.2 URL PX error javax.security.auth.login.LoginException in Tomcat 6/7

We have a published Knowledge Document (Note 1549998.1) describing one strange issue that with the correct usage of cookie authentication of URL PX deployed in Tomcat6/7 againt Agile PLM 9.3.2.0 we MAY continuously see below error. Error code : 60062Error message : Invalid username or passwordRoot Cause exception : javax.security.auth.login.LoginException: java.lang.SecurityException: User: cee71a234165ffc3:-5926181d:13fa9e51af6:-7ffd::e0FFUzoxMjh9REU3NDAyNjI4RENCOTYxMTExRkNCMDUwQzIwNjkxNzFCMkEx, failed to be authenticated. at com.agile.api.common.WebLogicAuthenticator.login(WebLogicAuthenticator.java:78) at com.agile.api.pc.Session.authenticate(Session.java:1123) at com.agile.api.pc.Session.(Session.java:216) ... at com.agile.api.AgileSessionFactory.createSession(AgileSessionFactory.java:927) at org.apache.jsp.login_jsp._jspService(login_jsp.java:91) ... at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) ... at java.lang.Thread.run(Thread.java:619) The Note describes that was originally introduced by parameter agile.sso.checkOneTimePXToken, which is used to increase the security of Agile authentication from external. "checkOneTimePXToken" will make Agile to use a different encode method to encrypt the cookie token, it may append a "=" symbol in the encrypted j_password cookie value. However by default, Tomcat 6/7 will ignore the "=" symbol and treat it as a second cookie. Below we will discuss how we identify the problem. We focus on how we think/analyze, not what the solution is. First let us code JSP page like below to create Agile session in URL PX which is deployed in Tomcat 6 or 7. Now we login Agile WebClient and use Wireshark to capture the TCP data, narrow to cookie section. As the cookies string is too long, Wireshark may truncate it. We can copy the value into notepad and get the whole cookie array like below. JSESSIONID=A9812A7FF1BDC8C65B26456AEDE35729invalidate_session=falsej_username=e0FFUzoxMjh9REU3NDAyNjI4RENCOTYxMTExRkNCMDUwQzIwNjkxNzFCMkExj_password=JSUle0FFUzoxMjh9ODgzQjI0RDM1Qjc0QzA5M0NDQUU0NUZFNjJBODU5QkYzNjFCMDMxQjQ2RjQwM0ZDRDVENTJBODMyNDIwOTBDRTgwQkRDQkREMDhEQkNGRkY4RDRDQzE4QjNCNDRFNzZBMTJGN0M2REQ1QzM3NTI1NEE0OUFGNDRFMTZBODRGODQ0ODQxOUZERTkzMzE3MjFGMEUwQUYzQjM2MTJGNTU1QzJCMTE=JSUl We notice there is a "=" in the tail of cookie "j_password". Then we trigger the URL PX, check the JSP page, we see below. j_username=e0FFUzoxMjh9REU3NDAyNjI4RENCOTYxMTExRkNCMDUwQzIwNjkxNzFCMkEx j_password=JSUle0FFUzoxMjh9ODgzQjI0RDM1Qjc0QzA5M0NDQUU0NUZFNjJBODU5QkYzNjFCMDMxQjQ2RjQwM0ZDRDVENTJBODMyNDIwOTBDRTgwQkRDQkREMDhEQkNGRkY4RDRDQzE4QjNCNDRFNzZBMTJGN0M2REQ1QzM3NTI1NEE0OUFGNDRFMTZBODRGODQ0ODQxOUZERTkzMzE3MjFGMEUwQUYzQjM2MTJGNTU1QzJCMTE Invalid username or password Absolutely "=JSUl" is lost from javax.servlet.http.Cookie value. This is Tomcat's behavior to ignore them intentionally. We can add below parameter to TOMCAT/conf/catalina.conf to avoid this. It is described in link http://tomcat.apache.org/tomcat-7.0-doc/config/systemprops.html org.apache.tomcat.util.http.ServerCookie.ALLOW_EQUALS_IN_VALUE=true In above link, there are another two parameter reminding us that some special characters also could be ignored if they are not enabled, these could be / , < and > . org.apache.tomcat.util.http.ServerCookie.ALLOW_HTTP_SEPARATORS_IN_V0org.apache.tomcat.util.http.ServerCookie.FWD_SLASH_IS_SEPARATOR

We have a published Knowledge Document (Note 1549998.1) describing one strange issue that with the correct usage of cookie authentication of URL PX deployed in Tomcat6/7 againt Agile PLM 9.3.2.0 we...

At most how many customized P3 attributes could be added into Agile?

I have one customer/Oracle Partner Consultant asking me such question: how many customized attributes can be allowed to add to Agile's subclass Page Three? I never did research against this because Agile User Guide never says this and theoretically Agile supports unlimited amount of customized attributes, unless the browser itself cannot handle them in allocated memory. However my customers says when to add almost 1000 attributes, the browser (Web Client) will not show any Page Three attributes, including all the out-of-box attributes. Let's see why. Analysis It is horrible to add 1000 attributes manually. Let's do it by a batch SQL like below to add them to Item's subclass Page Three tab. Do not execute below SQL because it will not take effect due to your different node id. CREATE OR REPLACE PROCEDURE createP3Text(v_name IN VARCHAR2) IS v_nid NUMBER;v_pid NUMBER;BEGIN select SEQNODETABLE.nextval into v_nid from dual; Insert Into nodeTable ( id,parentID,description,objType,inherit,helpID,version,name ) values ( v_nid,2473003, v_name ,1,0,0,0, v_name);Insert Into propertyTable ( ID,parentID,readOnly,attType,dataType,selection,visible,propertyID,value ) values ( SEQPROPERTYTABLE.nextval,v_nid,0,2,1,0,1,925, null);Insert Into propertyTable ( ID,parentID,readOnly,attType,dataType,selection,visible,propertyID,value ) values ( SEQPROPERTYTABLE.nextval,v_nid,1,0,0,0,0,1,'0');Insert Into propertyTable ( ID,parentID,readOnly,attType,dataType,selection,visible,propertyID,value ) values ( SEQPROPERTYTABLE.nextval,v_nid,1,0,0,0,0,2,'0');Insert Into propertyTable ( ID,parentID,readOnly,attType,dataType,selection,visible,propertyID,value ) values ( SEQPROPERTYTABLE.nextval,v_nid,1,2,2,0,1,3,'50');Insert Into propertyTable ( ID,parentID,readOnly,attType,dataType,selection,visible,propertyID,value ) values ( SEQPROPERTYTABLE.nextval,v_nid,0,2,1,0,1,5, null);Insert Into propertyTable ( ID,parentID,readOnly,attType,dataType,selection,visible,propertyID,value ) values ( SEQPROPERTYTABLE.nextval,v_nid,0,2,2,0,1,6,'50');Insert Into propertyTable ( ID,parentID,readOnly,attType,dataType,selection,visible,propertyID,value ) values ( SEQPROPERTYTABLE.nextval,v_nid,0,2,2,0,0,7,'0');Insert Into propertyTable ( ID,parentID,readOnly,attType,dataType,selection,visible,propertyID,value ) values ( SEQPROPERTYTABLE.nextval,v_nid,0,4,1,451,1,8,'0');Insert Into propertyTable ( ID,parentID,readOnly,attType,dataType,selection,visible,propertyID,value ) values ( SEQPROPERTYTABLE.nextval,v_nid,0,4,1,451,1,9,'1');Insert Into propertyTable ( ID,parentID,readOnly,attType,dataType,selection,visible,propertyID,value ) values ( SEQPROPERTYTABLE.nextval,v_nid,1,2,1,0,1,10,v_name);Insert Into propertyTable ( ID,parentID,readOnly,attType,dataType,selection,visible,propertyID,value ) values ( SEQPROPERTYTABLE.nextval,v_nid,1,0,0,0,0,11,'0');Insert Into propertyTable ( ID,parentID,readOnly,attType,dataType,selection,visible,propertyID,value ) values ( SEQPROPERTYTABLE.nextval,v_nid,1,4,1,11743,1,14,'2');Insert Into propertyTable ( ID,parentID,readOnly,attType,dataType,selection,visible,propertyID,value ) values ( SEQPROPERTYTABLE.nextval,v_nid,0,2,1,0,1,30, null);Insert Into propertyTable ( ID,parentID,readOnly,attType,dataType,selection,visible,propertyID,value ) values ( SEQPROPERTYTABLE.nextval,v_nid,0,2,1,0,1,38, null);Insert Into propertyTable ( ID,parentID,readOnly,attType,dataType,selection,visible,propertyID,value ) values ( SEQPROPERTYTABLE.nextval,v_nid,1,4,1,451,0,59,'1');Insert Into propertyTable ( ID,parentID,readOnly,attType,dataType,selection,visible,propertyID,value ) values ( SEQPROPERTYTABLE.nextval,v_nid,1,4,1,451,0,60,'1');Insert Into propertyTable ( ID,parentID,readOnly,attType,dataType,selection,visible,propertyID,value ) values ( SEQPROPERTYTABLE.nextval,v_nid,1,4,1,724,0,61, null);Insert Into propertyTable ( ID,parentID,readOnly,attType,dataType,selection,visible,propertyID,value ) values ( SEQPROPERTYTABLE.nextval,v_nid,1,2,1,0,0,232,'0');Insert Into propertyTable ( ID,parentID,readOnly,attType,dataType,selection,visible,propertyID,value ) values ( SEQPROPERTYTABLE.nextval,v_nid,1,4,1,451,0,233,'1');Insert Into propertyTable ( ID,parentID,readOnly,attType,dataType,selection,visible,propertyID,value ) values ( SEQPROPERTYTABLE.nextval,v_nid,0,4,1,12239,1,415,'13307');Insert Into propertyTable ( ID,parentID,readOnly,attType,dataType,selection,visible,propertyID,value ) values ( SEQPROPERTYTABLE.nextval,v_nid,1,2,1,0,0,605,'0');Insert Into propertyTable ( ID,parentID,readOnly,attType,dataType,selection,visible,propertyID,value ) values ( SEQPROPERTYTABLE.nextval,v_nid,0,4,1,451,1,610,'0');Insert Into propertyTable ( ID,parentID,readOnly,attType,dataType,selection,visible,propertyID,value ) values ( SEQPROPERTYTABLE.nextval,v_nid,1,4,1,451,0,716,'1');Insert Into propertyTable ( ID,parentID,readOnly,attType,dataType,selection,visible,propertyID,value ) values ( SEQPROPERTYTABLE.nextval,v_nid,0,4,1,451,1,795,'0');Insert Into propertyTable ( ID,parentID,readOnly,attType,dataType,selection,visible,propertyID,value ) values ( SEQPROPERTYTABLE.nextval,v_nid,0,4,1,2000008821,1,864,'2');Insert Into propertyTable ( ID,parentID,readOnly,attType,dataType,selection,visible,propertyID,value ) values ( SEQPROPERTYTABLE.nextval,v_nid,0,4,1,451,1,923,'0');Insert Into propertyTable ( ID,parentID,readOnly,attType,dataType,selection,visible,propertyID,value ) values ( SEQPROPERTYTABLE.nextval,v_nid,0,4,1,451,0,719,'0');Insert Into tableInfo ( tabID,tableID,classID,att,ordering ) values ( 2473005,1501,2473002,v_nid,9999);commit;END createP3Text;/BEGINFOR i in 1..1000 LOOP createP3Text('MyText' || i);END LOOP;END;/DROP PROCEDURE createP3Text;COMMIT; Now restart Agile Server and check the Server's log, we noticed below: ***** Node Created : 85625***** Property Created : 184579++++++++++++++++++++++++++++++++++++++ Agile PLM Server Starting Up... ++++++++++++++++++++++++++++++++++++++ However the previously log before batch SQL is ***** Node Created : 84625***** Property Created : 157579++++++++++++++++++++++++++++++++++++++ Agile PLM Server Starting Up... ++++++++++++++++++++++++++++++++++++++ Obviously we successfully imported 1000 (85625-84625) attributes. Now go to JavaClient and confirm if we have them or not. Theoretically we are able to open such item object and see all these 1000 attributes and their values, but we get below error. We have no error tips in server log. But never mind we have the Java Console for JavaClient. If to open the same item in JavaClient we get a clear error and detailed trace in Java Console. ORA-01795: maximum number of expressions in a list is 1000java.sql.SQLException: ORA-01795: maximum number of expressions in a list is 1000at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125) ... ...at weblogic.jdbc.wrapper.PreparedStatement.executeQuery(PreparedStatement.java:128)at com.agile.pc.cmserver.base.AgileFlexUtil.setFlexValuesForOneRowTable(AgileFlexUtil.java:1104)at com.agile.pc.cmserver.base.BaseFlexTableDAO.loadExtraFlexAttValues(BaseFlexTableDAO.java:111)at com.agile.pc.cmserver.base.BasePageThreeDAO.loadTable(BasePageThreeDAO.java:108) If you are interested in the background of the problem, you may de-compile the class com.agile.pc.cmserver.base.AgileFlexUtil.setFlexValuesForOneRowTable and find the root cause that Agile happens to hit Oracle Database's limitation that more than 1000 values in the "IN" clause. Check here http://ora-01795.ora-code.com If you need Oracle Agile's final solution, please contact Oracle Agile Support. Performance Below two screenshot are jvm heap usage from before-SQL and after-SQL. We can see there is no big memory gap between two cases. So definitely there is no performance impact to Agile Application Server unless you have more than 1000 attributes for EACH of your dozens of  subclasses. And for client, 1000 attributes should not impact the browser's performance because in HTML we only use dt and dd for each attribute's pair: label and value. It is quite lightweight.

I have one customer/Oracle Partner Consultant asking me such question: how many customized attributes can be allowed to add to Agile's subclass Page Three? I never did research against this because...

Develop PHP HTTP Destination for Agile PLM ACS

I seldom see customer use HTTP Destination for Agile ACS because of its code difficulty. But someone ever asked this question about how to code PHP program as the HTTP destination to process ACS data from Agile PLM. In our Agile PLM ACS User Guide, there is only one sample HTTP head data for reference like below. It is very abstract to understand and to develop the code. I will discuss this with details. After you read this article, you will also know how to write different HTTP Destination code with other languages like JSP and Asp.Net. Analyze HTTP Head First we setup a HTTP Destination in Agile JavaClient like below. There are something important I need to emphasize. URL: I use axis tcpmonitor as the HTTP Destination first because I need to collect all the HTTP head from Agile to see how it looks like. axis tcpmonitor is a open source tool and you can download from internet.Also attention there is a question mark in the end of URL. That is because Agile has a very small defect on this. Response Expected: Usually you can set it to No. If set to Yes, then you will be in a big trouble to send the expected Response data back to Agile. The Response is not HTTP Return Code. It is Agile specific. Request File Field: This field is used to simulate an HTML form element of below: So "acsFile" element is the key of File object in Post data.  Additional Parameters: You can use $_POST["para"] to get the posted data in httpreader.php Now let me trigger an ATO and transfer to HTTP Destination (It actually goes to axis tcpmonitor). After ATO is sent, we get the Head data now. ==== Request ====POST /httpreader.php HTTP/1.1User-Agent: Jakarta Commons-HttpClient/3.0Host: xxxxx.xxx.com:80Content-Length: 2319Content-Type: multipart/form-data; boundary=----------------314159265358979323846------------------314159265358979323846Content-Disposition: form-data; name="acsFile"; filename="TO0000557926.AXML"Content-Type: application/octet-stream; charset=ISO-8859-1Content-Transfer-Encoding: binaryxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx------------------314159265358979323846Content-Disposition: form-data; name="para2"Content-Type: text/plain; charset=US-ASCIIContent-Transfer-Encoding: 8bitmyvalue2------------------314159265358979323846Content-Disposition: form-data; name="para1"Content-Type: text/plain; charset=US-ASCIIContent-Transfer-Encoding: 8bitmyvalue1------------------314159265358979323846Content-Disposition: form-data; name="AgileRecordLocator"Content-Type: text/plain; charset=US-ASCIIContent-Transfer-Encoding: 8bitAAAt8QBj0vcAAC6TACW+uAAgRTJDRUVCNDc0MjAyNDlBOEI1MkYwRkUwQjJGOUFEQUU=------------------314159265358979323846-- Above Head data is real data that is sent from Agile system. We need to pay attention to these Post elements: 1. POSTFirst, it is POST, not GET 2. Content-Disposition: form-data; name="acsFile"; filename="TO0000557926.AXML" The File object is acsFile and filename is present in data. Then there is a very long binary characters of "xxxxxxxxxxxxxxxxx". It stands for the File stream. 3. Content-Disposition: form-data; name="para1"Content-Disposition: form-data; name="para2" para1 and para2 are customized by myself in Agile JavaClient and you can see the value are also present here: myvalue1, myvalue2 4. Content-Disposition: form-data; name="AgileRecordLocator"AgileRecordLocator is the unique identifier pointing to the specific ATO object. Each ATO object has unique AgileRecordLocator. In this example it is "AAAt8QBj0vcAAC6TACW+uAAgRTJDRUVCNDc0MjAyNDlBOEI1MkYwRkUwQjJGOUFEQUU=" httpreader.php to process ATO data If you understand how to write PHP code then you know below my code is to write the ATO data to a TO0000557926.AXML to "upload" directory. Response Expected This is very complicated. The Agile's expected response is not HTTP return code. Attention again, not HTTP return code like HTTP 200, HTTP 401 or HTTP 500. The correct expected response is an xml package from web service. First let's look at this Agile Web Service about ResponseService from below link: http://agile-server:port/Agile/integration/services/ResponseService?wsdl From above screenshot, we will know the operation of WebServce is acceptResponse and the input parameter is acceptResponseRequest which is a map of "recordLocator" (String), "accept" (boolean) and "msg"(String). So we need to combine such map into PHP array. $params = array('recordLocator'=>"AAAt8QBj0vcAAC6TACW+uAAgRTJDRUVCNDc0MjAyNDlBOEI1MkYwRkUwQjJGOUFEQUU=",'accept'=>true,'msg'=>"OK"); To send a webservice request from PHP, I use NuSOAP open source. Before call the operation of "acceptResponse", the credential data must be included to authenticate current request. $client->setCredentials($username,$password); And now we put the code together and we have below PHP file response.php . Attention, this PHP code must be one single file like response.php. That is to say, when Agile sends ACS data to httpreader.php and the ATO package file (PDX or AXML) is created on PHP web server, you need to run response.php manually. You may have to try to figure out how to trigger response.php automatically to send back Response (Accept or Reject) to Agile with the unique AgileRecourdLocator from ATO. There would be many ways, and you please think about it. Pay attention with two issues: 1. Do not put response.php code into httpreader.php, otherwise you will get below error, that is because to update Response status back to Agile cannot work in synchronization mode. com.agile.admin.client.value.AdminException: Please cancel this operation and refresh since a newer version of this object is available.at com.agile.admin.server.ADDAO.checkNodeVersion(ADDAO.java:247)at com.agile.admin.server.ADDAO.updateProperty(ADDAO.java:3059)at com.agile.admin.server.ADPropSSList.updateProperty(ADPropSSList.java:129)at com.agile.acs.PCExtractTask.setStatusSuccess(PCExtractTask.java:1835)at com.agile.acs.PCExtractTask.transmitPayload(PCExtractTask.java:1733)at com.agile.acs.PCExtractTask.tmpProcessWS(PCExtractTask.java:1530)at com.agile.acs.PCExtractTask.tmpExtractionProcess(PCExtractTask.java:758)at com.agile.acs.PCExtractTask.run(PCExtractTask.java:442)at java.util.TimerThread.mainLoop(Timer.java:512)at java.util.TimerThread.run(Timer.java:462) 2. If "Response Expected" is set to Yes in JavaClient, but you forget to run response.php, the ATO will always be in "Release" status and the "Transmission Status" will be "Pending" forever.

I seldom see customer use HTTP Destination for Agile ACS because of its code difficulty. But someone ever asked this question about how to code PHP program as the HTTP destination to process ACS data...

Agile JavaClient and Java Web Start

JavaClient uses Java Web Start technology to launch all required jar files and resources into local cache to deploy with online and offline mode. We will discuss how JavaClient is loaded from remote Application Server. Detect Java Web StartUsually we access JavaClient from the entrance http://host:port/JavaClient/start.html . This start.html will detect if Java Web Start is installed on local client machine with below script language in browser's engine. If the browser is not IE, we use navigator element and its function to detect application/x-java-jnlp-file. Else it will use VBScript in IE's engine to detect the object of JavaWebStart. The detailed code is like below. You also can refer to Oracle document in this link. http://docs.oracle.com/javase/7/docs/technotes/guides/javaws/developersguide/launch.html pcclient.jnlp pcclient.jnlp is the entrance point of JavaClient. We save the file to local and open in notepad. We will notice there is codebase defined as below: codebase="http://agile.mycompany.com:7001/JavaClient"That means all the jar and resource must be loaded from the base URL /JavaClient/. Below entry define the required data for JavaClient. It requires 1.6+ JRE, and defines the expected max heap size. The required jar and other expected extension resources in other referenced jnlp files. After all the resource is loaded into local cache, the main class com.agile.ui.pcclient.PCClient runs and asks for login. If we click the Option in login window, we see URL is pre-defined. It is actually read from the pcclient.jnlp. serverURL=t3://agile.mycompany.com:7001 Additionally information we can see directly from JavaClient is the webserverName, appserverVersion and UpdateVersions, all of them are defined here as well. Local CachePreviously we say all jar and resource are loaded from remote into local machine, if we go to java's cache directory we will see all of them.

JavaClient uses Java Web Start technology to launch all required jar files and resources into local cache to deploy with online and offline mode. We will discuss how JavaClient is loaded from...

Background task in PMON

We all understand that the principle task of PMON is to clean resource after abnormal end of connection process, monitor/recover the background process and register instance to a Lisener. This article will analyze the background task. Clean Resource of Process Disconnection. We debug the process with event 10246. As 10246 is not feasible to alter system dynamically, we start instance via pfile and add below parameter to initSID.ora file. event='10246 trace name context forever, level 10' Start instance and ensure the Event is working. [oracle@localhost ~]$ sqlplus sys/oracle as sysdbaidle> startupORACLE instance started....Database mounted.Database opened.idle> show parameter eventNAME TYPE VALUE-----------------------------------------------------------------event string 10246 trace name context forever, level 10xml_db_events string enable Then connect from remote client. C:\Users\JC>whoamichen-pc\jcC:\Users\JC>net view /domainDomain-------------------------------------ORADEV命令成功完成。C:\Users\JC>sqlplus agile/tartan@lxagile9SQL> select 1 from dual; 1---------- 1 Because of Dedicated Server connection mode, we use below SQL to get the Dedicated Server Connection process, client process, session id, session serial and session_paddr.SQL> select p.spid server_ospid, s.process client_ospid, s.sid session_id, s.serial# session_serial, s.paddr session_paddr from v$process p, v$session s where p.addr=s.paddr and s.sid=(select sid from v$mystat where rownum=1);SERVER_OSPID CLIENT_OSPID SESSION_ID SESSION_SERIAL SESSION_PADDR------------------------------------------------------------------------ 21374 7640:7644 11 12 3DFA87FC In above result we see Client_OSPID is shown as 7640:7644 on Windows. If on Unix, we will see a single number as the SQLPLUS process. On Windows, 7460 is the Process ID of SQLPLUS and 7644 is the Thread ID. To confirm this, we can use Process Explorer to verify. Let's go to server and kill process 21374. This process is the one which service the client's connection. idle> host[oracle@localhost ~]$ ps -ef|grep 21374oracle 21374 1 0 17:47 ? 00:00:00 oracleagile9 (LOCAL=NO)oracle 21397 21381 0 17:48 pts/1 00:00:00 grep 21374[oracle@localhost ~]$ kill -9 21374[oracle@localhost ~]$ exit On Client, DML will fail. SQL> select 1 from dual;select 1 from dual*第 1 行出现错误:ORA-03113: 通信通道的文件结尾进程 ID: 21374会话 ID: 11 序列号: 12 At this moment, Oracle will issue INTERRUPT exception and hand over to PMON to clean resource. We see the task in trace file. idle> show parameter background_dump_destNAME TYPE VALUE---------------------------------------------------------background_dump_dest string /u01/app/oracle/diag/rdbms/agile9/agile9/trace Trace file /u01/app/oracle/diag/rdbms/agile9/agile9/trace/agile9_pmon_21305.trc...*** 2013-03-16 17:49:20.938marked process 0x3dfa87fc pid=22 serial=5 ospid = 21374 dead client details: O/S info: user: JC, term: CHEN-PC, ospid: 7640:7644 machine: ORADEV\CHEN-PC program: sqlplus.exe application name: SQL*Plus, hash value=3669949024*** 2013-03-16 17:49:20.938deleting process 0x3dfa87fc pid=22 serial=5 priority=0deleting session 0x3d970a50 sid=11 serial=12deletion of process 3dfa87fc pid=22 seq=5 successful We see PMON delete the session and clean physical connection, release resource as well. Monitor Background Process We simulate a case that we kill the most important process manually, DBWn. Once this Process ends, the instance will stop immediately. idle> host[oracle@localhost ~]$ ps -ef | grep ora_ | grep $ORACLE_SIDoracle 21305 1 0 17:45 ? 00:00:00 ora_pmon_agile9oracle 21307 1 0 17:45 ? 00:00:00 ora_vktm_agile9...oracle 21323 1 0 17:45 ? 00:00:00 ora_dbw0_agile9oracle 21325 1 0 17:45 ? 00:00:00 ora_lgwr_agile9oracle 21327 1 0 17:45 ? 00:00:00 ora_ckpt_agile9oracle 21329 1 0 17:45 ? 00:00:00 ora_smon_agile9...oracle 21508 1 0 18:01 ? 00:00:00 ora_w000_agile9 [oracle@localhost ~]$ kill -9 21323[oracle@localhost ~]$ exit Then we issue DML query and should get ORA-03135. The Process ID 21341 is the connection process itself. idle> select 1 from dual;select 1 from dual*ERROR at line 1:ORA-03135: connection lost contactProcess ID: 21341Session ID: 125 Serial number: 5 Read PMON's trace file, we see PMON successfully detect that DBW0 ends. Background process DBW0 found dead*** 2013-03-16 18:02:36.901Oracle pid = 10OS pid (from detached process) = 21323 OS pid (from process state) = 21323dtp = 0x2000d868, proc = 0x3dfa05dcDump of memory from 0x2000D868 to 0x2000D8A02000D860 00000040 3DFA05DC [@......=]2000D870 00000000 00000000 30574244 00000200 [........DBW0....]2000D880 0000534B 0D898AFA B6EA36C0 0000534B [KS.......6..KS..]2000D890 0D898AFA 00000001 2721298A 00010000 [.........)!'....]Dump of memory from 0x3DFA05DC to 0x3DFA10B43DFA05D0 00000301 [....]3DFA05E0 00000302 00000000 3DFA05DC 3DFA05EC [...........=...=]3DFA05F0 3DFA05EC 00000000 00000000 00000000 [...=............]3DFA0600 00000000 00000000 00000019 3CE4A084 [...............<]3DFA0610 3D134570 3DB13248 3D133848 00000000 [pE.=H2.=H8.=....]3DFA0620 3D1338BC 3D1338BC 3D134560 00000601 [.8.=.8.=`E.=....]3DFA0630 00000000 3D9808D8 3DB13248 0000000A [.......=H2.=....]3DFA0640 00000000 0000000A 00000001 874B9F64 [............d.K.]3DFA0650 3A79CCE4 00000000 00000000 00000000 [..y:............]3DFA0660 00000000 3D778FDC 3D7790C0 00000000 [......w=..w=....]3DFA0670 00000000 00000000 00000000 00000000 [................] Repeat 67 times3DFA10A0 3DFA10A0 3DFA10A0 00000000 3DFA10AC [...=...=.......=]3DFA10B0 3DFA10AC [...=] *** 2013-03-16 18:02:36.903PMON (ospid: 21305): terminating the instance due to error 471*** 2013-03-16 18:02:46.933Instance termination failed to kill one or more processesksuitm_check: OS PID=21341 is still alive PMON then close almost all process except 21341, which is the current connection process on server. We see same error in alert.log file as well. Sat Mar 16 18:02:36 2013PMON (ospid: 21305): terminating the instance due to error 471Termination issued to instance processes. Waiting for the processes to exitSat Mar 16 18:02:46 2013Instance termination failed to kill one or more processesInstance terminated by PMON, pid = 21305 Above information mentions error 471, it is actually ORA-000471. ORA-00471: DBWR process terminated with errorCause: The database writer process diedAction: Warm start instance Register Instance to Listener Before we start, we need to start Oracle database, stop Listener and delete listener.ora. That means we are using dynamic register for Listener.LSNRCTL> stopConnecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))The command completed successfullyLSNRCTL> We get the PMON process in OS level. idle> select SPID,PROGRAM from v$process where PROGRAM like '%PMON%';SPID PROGRAM------------------------ ------------------------------------------------29172 oracle@localhost.localdomain (PMON) Now debug with oradebug idle> oradebug setospid 29172Oracle pid: 2, Unix process pid: 29172, image: oracle@localhost.localdomain (PMON)idle> oradebug Event 10257 trace name context forever, level 16Statement processed. Again, we start Listener LSNRCTL> startStarting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...TNSLSNR for Linux: Version 11.2.0.1.0 - ProductionLog messages written to /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xmlListening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))STATUS of the LISTENER------------------------Alias LISTENERVersion TNSLSNR for Linux: Version 11.2.0.1.0 - ProductionStart Date 17-MAR-2013 11:33:07Uptime 0 days 0 hr. 0 min. 0 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Log File /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xmlListening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))The listener supports no servicesThe command completed successfully Issue "status" command several times we see there is still no instance registered to Listener. LSNRCTL> statusConnecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))STATUS of the LISTENER------------------------Alias LISTENERVersion TNSLSNR for Linux: Version 11.2.0.1.0 - ProductionStart Date 17-MAR-2013 11:33:07Uptime 0 days 0 hr. 0 min. 3 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Log File /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xmlListening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))The listener supports no servicesThe command completed successfully After a while, we find there is an instance registered automatically. LSNRCTL> statusConnecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))STATUS of the LISTENER------------------------Alias LISTENERVersion TNSLSNR for Linux: Version 11.2.0.1.0 - ProductionStart Date 17-MAR-2013 11:33:07Uptime 0 days 0 hr. 2 min. 19 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Log File /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xmlListening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))Services Summary...Service "agile9" has 1 instance(s). Instance "agile9", status READY, has 1 handler(s) for this service...The command completed successfully End oradebug, let's check PMON's trace log. Attention you will see below two lines in such log. It shows the PMON automatically registered the instance to Listener after 60 seconds (defult value is 60 seconds). kmmgdnu: agile9 kmmlrl: instance load 1 idle> oradebug tracefile_name/u01/app/oracle/diag/rdbms/agile9/agile9/trace/agile9_pmon_29172.trcidle> oradebug Event 10257 trace name context OFF;Statement processed. *** 2013-03-17 11:32:46.658kmmlrl: update for process drop delta: 52 52 24 26 149kmmgdnu: agile9 goodness=0, delta=1, flags=0x4:unblocked/not overloaded, update=0x6:G/D/-kmmlrl: 24 processeskmmlrl: instance load 1kmmlrl: nsgr update returned 0

We all understand that the principle task of PMON is to clean resource after abnormal end of connection process, monitor/recover the background process and register instance to a Lisener. This article...

Poorer performance of IQuery in 9.3.0.3 than 9.3.0.1

Recently we got several customers' report that the IQuery API in client SDK program has poorer performance in Agile 9.3.0.3 than 9.3.0.1. If to monitor SDK client program's memory heap usage we will see much more heap is used in 9.3.0.3 and Wireshark monitor shows more data packages are received from Agile Server. This article will bring us close to the essence of the problem and see the different behavior between 9.3.0.3 and 9.3.0.1. First we have a very simple IQuery sample code to get all active users from Agile. After the IQuery finished, we call Thread.sleep(1000000) to pause the program, then take the heap dump. public static ArrayList getUsersData(IAgileSession agileSession) throws Exception { ArrayList result = null; try { String query = "SELECT [General Info.User ID], "; query += " [General Info.First Name], "; query += " [General Info.Last Name], "; query += " [General Info.Business Phone], "; query += " [General Info.Status], "; query += " [General Info.Email], "; query += " [General Info.LOB], "; query += " [General Info.BU], "; query += " [General Info.Division], "; query += " [General Info.Department], "; query += " [General Info.Title], "; query += " [General Info.Address], "; query += " [General Info.Mobile Phone], "; query += " [General Info.Role], "; query += " [General Info.Region], "; query += " [General Info.Country], "; query += " [Detail Information.User Group] , "; query += " [Detail Information.RO User], "; query += " [Detail Information.Brand], "; query += " [Detail Information.NS], "; query += " [Detail Information.LOCATED RO], "; query += " [Detail Information.LOCATED NS], "; query += " [Detail Information.Business Group] "; query += "FROM [User] "; query += "WHERE [General Info.Status] == 'Active' "; IQuery iQuery = (IQuery) agileSession.createObject(IQuery.OBJECT_TYPE, query); ITable table = iQuery.execute(); table.setPageSize(1000); ITwoWayIterator it = table.getTableIterator(); result = new ArrayList(); int i=0; while (it.hasNext()) { IRow row = (IRow) it.next(); Map map = row.getValues(); System.out.println(i++ +" : " +map); result.add(map); if(i == 1000 ) break; } printUsedMemory("KB"); } catch (APIException e) { throw e; } return result; } In above code, we fill result (ArrayList) with 1000 objects, it is for diagnosis only. Now we have two Heap Dump files, one for 9.3.0.3 and the rest one for 9.3.0.1. In 9.3.0.1, we have 1000 objects in ArrayList, they consumes 5,770,544 bytes, while 9.3.0.3 consumes 206,871,844 bytes. Address0x17b92198Namearray of [Ljava/lang/Object;Number of children1,000Number of parents1Owner address0x17ec3ee0Owner objectjava/util/ArrayListSize4,076Total size5,770,544 Leak suspectResponsible for 206,871,844 bytes (93.252 %) of Java heapAddress0x22780c40Namearray of [Ljava/lang/Object;Number of children1,000Number of parents1Owner address0x17f429c8Owner objectjava/util/ArrayListSize4,076Total size206,871,844 Definitely, each object in 9.3.0.3 consumes more memory than 9.3.0.1. Let's capture one attribute [General Info.Department] to analyze. "Department" is a List type, of course we do not care it is common List or Cascade List. Compare this attribute between two version we will see the big difference and confirm there is a big object (com/agile/api/pc/CascadeList) in 9.3.0.3 which consumes 100, 199 bytes, 9.3.0.1 consumes 248 bytes only. Look more close to the big object (com/agile/api/pc/CascadeList) in 9.3.0.3, we should see com/agile/admin/client/value/AdminList, it takes 99, 947 bytes and contains 579 arrays of com/agile/admin/client/value/AdminListItem , actually AdminListItem represent one List entry object. From here we realize that in 9.3.0.3, if to return a List attribute value, Agile also will return the whole List's all entries value to the client. But 9.3.0.1 will not. So, that is the difference and you get it, right? Please contact Oracle Agile Support if you consider the performance impact to your business and system.

Recently we got several customers' report that the IQuery API in client SDK program has poorer performance in Agile 9.3.0.3 than 9.3.0.1. If to monitor SDK client program's memory heap usage we will...

Checksum in Agile File Server

We will discuss the concept of checksum in Agile PLM and related technology. If enabled Checksum Calculation in Agile PLM, we may see below error when do CheckOut/CheckIn file attachment. This scenario means the original physical file is modified by external program beyond Agile. There are quite a few ways to calculate the checksum against file, for example SHA1, MD5 and CRC32. We Agile PLM use CRC32 to verify file. More information of detailed algorithm and concept of CRC32 please read this document: http://en.wikipedia.org/wiki/Cyclic_redundancy_check File Checksum in Agile Upon uploading file attachment each time, the new calculated checksum value will be saved to table file_info (column checksum_value) in database. Before Get/Checkout, Agile calculates the checksum value against phifical file in File Manager vault and compares it with the one in database. Checksum calculation requires additional CPU time. For huge file Agile may delay few seconds (you may or may not notice the delay because of human's sense). Let's enable the DEBUG option for File Manager and monitor the delayed timestamps.1. When to upload 729,207,676 bytes file(about 720M) log shows checksum takes 2.078 seconds <2012-12-09 19:51:23,545>Entering updateChecksum => File ID:6020222 Vault Type :Standard Vault : Primary VaultRelativeFilePath :000/060/202/AGILE_16020222.zip<2012-12-09 19:51:23,559>Inside postCheckIn =>ServerContext:com.agile.webfs.components.security.ServerContext@d306dd File ID :6020222Checksum value :0 EIFS filepath :null IFS filepath :000/060/202/AGILE_16020222.zip HFS filepath :null Locations :http://localhost:8080/Filemgr/services/FileServer File type :zip<2012-12-09 19:51:23,559>Checksum Enabled:true<2012-12-09 19:51:25,637>Computed checksum value: 3309565842<2012-12-09 19:51:25,637>Action:Checksum::postCheckIn Time Taken:2.078 secs<2012-12-09 19:51:25,637>Adding file information fileID :6020222 EIFS filepath :null IFS filepath :000/060/202/AGILE_16020222.zip HFS filepath :null Locations :http://localhost:8080/Filemgr/services/FileServer File type :zipPersistence Level :1<2012-12-09 19:51:25,637>Action:EventDispatcher::postCheckIn Time Taken:2.078 secs<2012-12-09 19:51:25,637>Action:Vault:: updateChecksum Time Taken:2.092 secs<2012-12-09 19:51:25,637>Leaving updateCheckSum 2. Download the same file, checksum will be calculated again and it taks 2.559 seconds. <2012-12-09 20:18:49,719>Checksum Enabled:true<2012-12-09 20:18:52,278>Computed Checksum value: 3309565842<2012-12-09 20:18:52,278>Action:Checksum::preCheckOut Time Taken:2.559 secs Above calculations are done in Tomcat's JVM, it is recommended to have powerful CPU on Tomcat machine as well. Checksum in other applications We can use Checksum in many application developments to meet the software (or business) requirement. It is very convenient to reference the API java.util.zip.CRC32 .Below sample code shows how to use this API to get the Checksum value of a huge file with 1,444,792,736 bytes(around 1.2G). It takes 14342 milliseconds. We only need to pay attention that the value here is denary. package zigzag.research.checksum;import java.io.*;import java.util.zip.CRC32;public class ChecksumCalc { public static void main(String args[]) { final int BUFFER_SIZE = 1024; byte[] buffer = new byte[BUFFER_SIZE]; CRC32 checksum = new CRC32(); InputStream is = null; int length; long begin = System.currentTimeMillis(); long end; try { is = new FileInputStream(new File("d:\\java_pid3256.hprof")); checksum.reset(); while ((length = is.read(buffer, 0, BUFFER_SIZE)) != -1) { checksum.update(buffer, 0, length); } end = System.currentTimeMillis(); System.out.println("checksum value=" + checksum.getValue() + ", time=" + (end-begin) + "ms"); } catch (Exception e) { e.printStackTrace(); } }} Execution Result: D:\Program\Java\jdk1.5.0_07\bin\java zigzag.research.checksum.ChecksumCalcchecksum value=2151428387, time=14342ms Checksum in Software Download It is widely used in software download website to avoid the original software is corrupted by virus or cracker. Below screenshot shows the zip file with a valid SHA1 Checksum value. Then we can use some free utility to calculate the downloaded file's checksum and compare with the valid one shown in website. A useful tool is HashCalc and you please download from this link, http://www.slavasoft.com/hashcalc/index.htm If use this tool, we can get above file's Checksum value and get hexadecimal 803C3123 which equals to denary 2151428387.

We will discuss the concept of checksum in Agile PLM and related technology. If enabled Checksum Calculation in Agile PLM, we may see below error when do CheckOut/CheckIn file attachment....

Background execution of IQuery API in Agile SDK

I have been asked by customers and partners about one same puzzle that why IQuery.execute() runs extremely fast, while the first time execution of Iterator.hasNext() runs very slow。To answer and summarize the background technology of IQuery, let's analyze it detailedly. Below is a very typical usage of IQuery. String sql = "[1001] contains '247'";IQuery query = (IQuery) session.createObject(IQuery.OBJECT_TYPE,ItemConstants.CLASS_PARTS_CLASS);query.setCaseSensitive(false);query.setCriteria(sql);query.setResultAttributes(new Object[] { 1001, 1002, 1014, 1016, 1017, 1081, 1082 , 1084, 12089, 2000002781, 2000002859, 2000004143});ITable tabs = query.execute();tabs.setPageSize(0);Iterator iterator = tabs.iterator();while(iterator.hasNext()){IRow row = (IRow)iterator.next();t0 = System.currentTimeMillis();} To better watch its background execution, we deploy this program as Process Extension, also enable SQL DEBUG option on Agile Application. We also add the -Ddisable.tasks=true parameter to JVM to filter out other SQL output. Now we print the timestamp in the code and check the system log. Create IQuery Instance t0 = System.currentTimeMillis();IQuery query = (IQuery) session.createObject(IQuery.OBJECT_TYPE,ItemConstants.CLASS_PARTS_CLASS);System.out.println("createObject: " + (System.currentTimeMillis() - t0)); Log output of createObject <2013-01-23 04:07:06,287>execute (Elapsed Time = 16 ms): "INSERT INTO QUERY (ID, NAME, TYPE, OWNER, OBJVERSION, FLAGS, CASE_SENSITIVE) VALUES (32134262, '32134262', 10000, 704, 0, '00000000000000000000000000000000', 1)"13/01/23 04:07:06 createObject: 1110 Set Case Sensitive t0 = System.currentTimeMillis();query.setCaseSensitive(false);System.out.println("setCaseSensitive: " + (System.currentTimeMillis() - t0)); Log output of setCaseSensitive <2013-01-23 04:07:06,428>executeQuery (Elapsed Time = 16 ms): "SELECT A.ID, 5, 5, OBJVERSION, 0 , NAME FROM QUERY A WHERE ID = 32134262"<2013-01-23 04:07:06,428>executeQuery (Elapsed Time = 0 ms): "SELECT OBJVERSION, ID FROM QUERY WHERE ID IN ( 32134262 ) FOR UPDATE NOWAIT "<2013-01-23 04:07:06,428>executeQuery (Elapsed Time = 0 ms): "SELECT A.ID, 5, 5, OBJVERSION, 0 , NAME FROM QUERY A WHERE ID = 32134262"<2013-01-23 04:07:06,428>executeQuery (Elapsed Time = 0 ms): "SELECT NAME, TYPE, OWNER, IS_PUBLIC, CASE_SENSITIVE, OBJVERSION, START_AT, RANGE, WHERE_USED_TYPE, FLAGS, SORT_COLUMNS, SORT_ORDER, GROUP_COLUMNS, REL_OBJ_CLASS, LOCKED_ATT FROM QUERY WHERE ID = 32134262"<2013-01-23 04:07:06,428>executeQuery (Elapsed Time = 0 ms): "SELECT att_id, width FROM SELECT_LIST WHERE query_id = 32134262 ORDER BY seq_id"<2013-01-23 04:07:06,428>executeUpdate (Elapsed Time = 0 ms): "UPDATE QUERY SET CASE_SENSITIVE = '0' WHERE ID = 32134262"<2013-01-23 04:07:06,443>executeUpdate (Elapsed Time = 0 ms): "UPDATE QUERY SET OBJVERSION = NVL(OBJVERSION,0)+ 1 WHERE ID = 32134262"<2013-01-23 04:07:06,443>executeQuery (Elapsed Time = 0 ms): "SELECT A.ID, 5, 5, OBJVERSION, 0 , NAME FROM QUERY A WHERE ID = 32134262"13/01/23 04:07:06 setCaseSensitive: 47 Set Criteria for Query: t0 = System.currentTimeMillis();query.setCriteria(sql);System.out.println("setCriteria: " + (System.currentTimeMillis() - t0)); Log output setCriteria: <2013-01-23 04:07:06,474>executeQuery (Elapsed Time = 0 ms): "SELECT A.ID, 5, 5, OBJVERSION, 0 , NAME FROM QUERY A WHERE ID = 32134262"<2013-01-23 04:07:06,474>executeQuery (Elapsed Time = 0 ms): "SELECT NAME, TYPE, OWNER, IS_PUBLIC, CASE_SENSITIVE, OBJVERSION, START_AT, RANGE, WHERE_USED_TYPE, FLAGS, SORT_COLUMNS, SORT_ORDER, GROUP_COLUMNS, REL_OBJ_CLASS, LOCKED_ATT FROM QUERY WHERE ID = 32134262"<2013-01-23 04:07:06,474>executeQuery (Elapsed Time = 0 ms): "SELECT att_id, width FROM SELECT_LIST WHERE query_id = 32134262 ORDER BY seq_id"<2013-01-23 04:07:06,506>executeQuery (Elapsed Time = 0 ms): "SELECT OBJVERSION, ID FROM QUERY WHERE ID IN ( 32134262 ) FOR UPDATE NOWAIT "<2013-01-23 04:07:06,506>executeUpdate (Elapsed Time = 0 ms): "DELETE FROM CRITERIA WHERE QUERY_ID = 32134262"<2013-01-23 04:07:06,506>executeUpdate (Elapsed Time = 0 ms): "UPDATE QUERY SET OBJVERSION = NVL(OBJVERSION,0)+ 1 WHERE ID = 32134262"<2013-01-23 04:07:06,506>executeQuery (Elapsed Time = 0 ms): "SELECT A.ID, 5, 5, OBJVERSION, 0 , NAME FROM QUERY A WHERE ID = 32134262"<2013-01-23 04:07:06,521>executeQuery (Elapsed Time = 15 ms): "SELECT NAME, TYPE, OWNER, IS_PUBLIC, CASE_SENSITIVE, OBJVERSION, START_AT, RANGE, WHERE_USED_TYPE, FLAGS, SORT_COLUMNS, SORT_ORDER, GROUP_COLUMNS, REL_OBJ_CLASS, LOCKED_ATT FROM QUERY WHERE ID = 32134262"<2013-01-23 04:07:06,521>executeQuery (Elapsed Time = 0 ms): "SELECT att_id, width FROM SELECT_LIST WHERE query_id = 32134262 ORDER BY seq_id"<2013-01-23 04:07:06,537>executeQuery (Elapsed Time = 0 ms): "SELECT OBJVERSION, ID FROM QUERY WHERE ID IN ( 32134262 ) FOR UPDATE NOWAIT "<2013-01-23 04:07:06,537>executeQuery (Elapsed Time = 0 ms): "SELECT A.ID, 5, 5, OBJVERSION, 0 , NAME FROM QUERY A WHERE ID = 32134262"<2013-01-23 04:07:06,537>executeQuery (Elapsed Time = 0 ms): "SELECT NAME, TYPE, OWNER, IS_PUBLIC, CASE_SENSITIVE, OBJVERSION, START_AT, RANGE, WHERE_USED_TYPE, FLAGS, SORT_COLUMNS, SORT_ORDER, GROUP_COLUMNS, REL_OBJ_CLASS, LOCKED_ATT FROM QUERY WHERE ID = 32134262"<2013-01-23 04:07:06,537>executeQuery (Elapsed Time = 0 ms): "SELECT att_id, width FROM SELECT_LIST WHERE query_id = 32134262 ORDER BY seq_id"<2013-01-23 04:07:06,537>executeBatch (Elapsed Time = 0 ms): "INSERT INTO CRITERIA (ID, ROW_ID, QUERY_ID, ATTR_ID, RELATIONAL_OP, VALUE, LOGICAL_OP, LEFT_PAREN, RIGHT_PAREN, SET_OPERATOR, RELEXPRESSION_OP, FLAGS) SELECT 32134263, NVL(MAX(ROW_ID) + 1, 0), 32134262, 1001, 1, '247', 0, 0, 0, 0, 0, '00000000000000000000000000000000' FROM CRITERIA WHERE QUERY_ID = 32134262"<2013-01-23 04:07:06,537>executeUpdate (Elapsed Time = 0 ms): "UPDATE QUERY SET OBJVERSION = NVL(OBJVERSION,0)+ 1 WHERE ID = 32134262"13/01/23 04:07:06 setCriteria: 94 Set the Result Layout: t0 = System.currentTimeMillis();query.setResultAttributes(new Object[] { 1001, 1002, 1014, 1016, 1017, 1081, 1082 , 1084, 12089, 2000002781, 2000002859, 2000004143});System.out.println("setResultAttributes: " + (System.currentTimeMillis() - t0)); Log output of setResultAttributes: <2013-01-23 04:07:06,553>executeQuery (Elapsed Time = 0 ms): "SELECT A.ID, 5, 5, OBJVERSION, 0 , NAME FROM QUERY A WHERE ID = 32134262"<2013-01-23 04:07:06,553>executeQuery (Elapsed Time = 0 ms): "SELECT NAME, TYPE, OWNER, IS_PUBLIC, CASE_SENSITIVE, OBJVERSION, START_AT, RANGE, WHERE_USED_TYPE, FLAGS, SORT_COLUMNS, SORT_ORDER, GROUP_COLUMNS, REL_OBJ_CLASS, LOCKED_ATT FROM QUERY WHERE ID = 32134262"<2013-01-23 04:07:06,553>executeQuery (Elapsed Time = 0 ms): "SELECT att_id, width FROM SELECT_LIST WHERE query_id = 32134262 ORDER BY seq_id"<2013-01-23 04:07:06,943>executeQuery (Elapsed Time = 0 ms): "SELECT OBJVERSION, ID FROM QUERY WHERE ID IN ( 32134262 ) FOR UPDATE NOWAIT "<2013-01-23 04:07:06,943>executeQuery (Elapsed Time = 0 ms): "SELECT A.ID, 5, 5, OBJVERSION, 0 , NAME FROM QUERY A WHERE ID = 32134262"<2013-01-23 04:07:06,959>executeQuery (Elapsed Time = 0 ms): "SELECT NAME, TYPE, OWNER, IS_PUBLIC, CASE_SENSITIVE, OBJVERSION, START_AT, RANGE, WHERE_USED_TYPE, FLAGS, SORT_COLUMNS, SORT_ORDER, GROUP_COLUMNS, REL_OBJ_CLASS, LOCKED_ATT FROM QUERY WHERE ID = 32134262"<2013-01-23 04:07:06,959>executeQuery (Elapsed Time = 0 ms): "SELECT att_id, width FROM SELECT_LIST WHERE query_id = 32134262 ORDER BY seq_id"<2013-01-23 04:07:06,959>executeUpdate (Elapsed Time = 0 ms): "DELETE FROM SELECT_LIST WHERE QUERY_ID = 32134262"<2013-01-23 04:07:06,959>executeBatch (Elapsed Time = 0 ms): "INSERT INTO select_list(query_id, seq_id, att_id, width) VALUES (32134262, 11, 2000004143, 0)"<2013-01-23 04:07:06,959>executeUpdate (Elapsed Time = 0 ms): "UPDATE QUERY SET OBJVERSION = NVL(OBJVERSION,0)+ 1 WHERE ID = 32134262"13/01/23 04:07:06 setResultAttributes: 422 Execute the Query, set Pagesize and read the collection: t0 = System.currentTimeMillis();ITable tabs = query.execute();System.out.println("execute: " + (System.currentTimeMillis() - t0));t0 = System.currentTimeMillis();tabs.setPageSize(0);System.out.println("setPageSize: " + (System.currentTimeMillis() - t0));t0 = System.currentTimeMillis();Iterator iterator = tabs.iterator();System.out.println("iterator: " + (System.currentTimeMillis() - t0)); Log output shows above executions do not call SQL. The real SQL query is invoked by hasNext() function. 13/01/23 04:07:06 execute: 013/01/23 04:07:06 setPageSize: 013/01/23 04:07:06 iterator: 0 Read the query result in while loop. t0 = System.currentTimeMillis();while(iterator.hasNext()){System.out.println("hasNext: " + (System.currentTimeMillis() - t0));t0 = System.currentTimeMillis();IRow row = (IRow)iterator.next();System.out.println("next: " + (System.currentTimeMillis() - t0));t0 = System.currentTimeMillis();} Log output <2013-01-23 04:07:06,990>executeQuery (Elapsed Time = 0 ms): "SELECT A.ID, 5, 5, OBJVERSION, 0 , NAME FROM QUERY A WHERE ID = 32134262"<2013-01-23 04:07:06,990>executeQuery (Elapsed Time = 0 ms): "SELECT NAME, TYPE, OWNER, IS_PUBLIC, CASE_SENSITIVE, OBJVERSION, START_AT, RANGE, WHERE_USED_TYPE, FLAGS, SORT_COLUMNS, SORT_ORDER, GROUP_COLUMNS, REL_OBJ_CLASS, LOCKED_ATT FROM QUERY WHERE ID = 32134262"<2013-01-23 04:07:06,990>executeQuery (Elapsed Time = 0 ms): "SELECT att_id, width FROM SELECT_LIST WHERE query_id = 32134262 ORDER BY seq_id"<2013-01-23 04:07:07,006>executeQuery (Elapsed Time = 16 ms): "SELECT ID, ROW_ID, ATTR_ID, RELATIONAL_OP, VALUE, LOGICAL_OP, LEFT_PAREN, RIGHT_PAREN, FLAGS, SET_OPERATOR, RELEXPRESSION_OP, PROMPT, PARAM_INDEX FROM CRITERIA WHERE QUERY_ID = 32134262 ORDER BY ROW_ID"<2013-01-23 04:07:07,084>executeQuery (Elapsed Time = 31 ms): "select /*+ ALL_ROWS */ ITEM_P2P3_QUERY.ID,ITEM_P2P3_QUERY.CLASS,ITEM_P2P3_QUERY.SUBCLASS,ITEM_P2P3_QUERY.FLAGS,ITEM_P2P3_QUERY.REV_FLAGS,NULL,NULL,ITEM_P2P3_QUERY.ITEM_NUMBER,ITEM_P2P3_QUERY.ITEM_NUMBER,ITEM_P2P3_QUERY.DESCRIPTION,ITEM_P2P3_QUERY.DESC_REV,ITEM_P2P3_QUERY.REV_NUMBER,ITEM_P2P3_QUERY.LATEST_RELEASED_ECO, TO_Char(ITEM_P2P3_QUERY.RELEASE_DATE, 'YYYY-MM-DD HH24:MI:SS'), TO_Char(ITEM_P2P3_QUERY.INCORP_DATE, 'YYYY-MM-DD HH24:MI:SS'),ITEM_P2P3_QUERY.SUBCLASS,ITEM_P2P3_QUERY.CATEGORY,ITEM_P2P3_QUERY.RELEASE_TYPE, TO_Char(ITEM_P2P3_QUERY.EFFECTIVE_DATE, 'YYYY-MM-DD HH24:MI:SS'),ITEM_P2P3_QUERY.IS_TLA,ITEM_P2P3_QUERY.EXCLUDE_FROM_ROLLUP, TO_Char(ITEM_P2P3_QUERY.COMPLIANCY_CALC_DATE, 'YYYY-MM-DD HH24:MI:SS'),ITEM_P2P3_QUERY.CREATE_USER,ITEM_P2P3_QUERY.MULTILIST06,ITEM_P2P3_QUERY.CREATE_USER,NULL,NULL,ITEM_P2P3_QUERY.ITEM_NUMBER from ITEM_P2P3_QUERY where (ITEM_P2P3_QUERY.ITEM_NUMBER LIKE '%247%' ESCAPE '\') AND ITEM_P2P3_QUERY.CLASS = 10000 AND (NVL(ITEM_P2P3_QUERY.DELETE_FLAG,0) = 0) ORDER BY 28"13/01/23 04:07:07 hasNext: 87513/01/23 04:07:07 next: 013/01/23 04:07:07 hasNext: 013/01/23 04:07:07 next: 013/01/23 04:07:07 hasNext: 0......... We get the point that the real SQL query is called by the first time of Iterator.hasNext(), which loads all the result into local JVM heap. Subsequent hasNext() and next() read data from local heap and almost do not consume CPU time. Iterator.hasNext() You may be interested why Iterator.hasNext() can trigger SQL call. If we debug the program, we will see the returned type of ITable tabs = query.execute(); is acturally TableQueryResults. And Iterator iterator = tabs.iterator(); returns TableIterator. Below analysis is only for research. If to De-compile com.agile.api.pc.query.Query from SDK.jar, you will see an inner class ExecuteAction, which defines the returned type of IQuery.execute(). ITable.iterator() calls TableQueryResults.iterator(). TableQueryResults' super class (com.agile.api.pc.Table) implemented the concrete iterator(). To call Iterator.hasNext(), it is acturally to call TableIterator.hasNext(). And the SQL query is invocked by checkIterator(). Likewise, next() function is defined in TableIterator and invocked by TableIterator instance, not Iterator.

I have been asked by customers and partners about one same puzzle that why IQuery.execute() runs extremely fast, while the first time execution of Iterator.hasNext() runs very slow。To answer...

Analysis against JVM Thread Dump - Out Of Thread

Out Of Thread means there are many tasks (requests) are pending to run, but only a few Threads are available. Many queued tasks have to wait for running threads to be idle. It is a much more complicated case and it is very hard to be identified from Thread Dump log. To dig out the root cause it requires the analyst have much knowledge on the Thread Pool technology. We will have a much more complex source code to demonstrate this. Demonstration We define a class ThreadPool which creates a group of a limited number of threads that are used to execute queued tasks.It contains below functions ThreadPool(): The constructor to initialize a group of threads (several PooledThread instances). runTask(): Requests a new task to run. This method returns immediately and the task executes on the next available idle thread in this ThreadPool. getTask(): Returns the next pending task to run. close(): Force to stop all threads (PooledThread instances), no matter the thread is running or queued to run. join(): Closes this ThreadPool and waits for all running threads to finish. Any waiting tasks are executed. ThreadPool also has an inner class PooledThread which extends Thread abstract class, it is used to create the concrete running thread and invoke the target task. private class PooledThread extends Thread { public PooledThread() { super(ThreadPool.this, "Zigzag_Thread-" + (threadID++)); } public void run() { while (!isInterrupted()) { // get a task to run Runnable task = null; try { task = getTask(); } catch (InterruptedException ex) { } // if getTask() returned null or was interrupted, // close this thread by returning. if (task == null) { return; } // run the task, and eat any exceptions it throws try { task.run(); } catch (Throwable t) { uncaughtException(this, t); } } } } So put it together to see how the ThreadPool is defined, like below. class ThreadPool extends ThreadGroup { private boolean isAlive; private LinkedList taskQueue; private int threadID; public ThreadPool(int numThreads) { super("Zigzag_ThreadPool"); setDaemon(true); isAlive = true; taskQueue = new LinkedList(); for (int i = 0; i < numThreads; i++) { new PooledThread().start(); } } public synchronized void runTask(Runnable task) { if (!isAlive) { throw new IllegalStateException(); } if (task != null) { taskQueue.add(task); notify(); } } protected synchronized Runnable getTask() throws InterruptedException { while (taskQueue.size() == 0) { if (!isAlive) { return null; } wait(); } return (Runnable) taskQueue.removeFirst(); } public synchronized void close() { if (isAlive) { isAlive = false; taskQueue.clear(); interrupt(); } } public void join() { synchronized (this) { isAlive = false; notifyAll(); } Thread[] threads = new Thread[activeCount()]; int count = enumerate(threads); for (int i = 0; i < count; i++) { try { threads[i].join(); } catch (InterruptedException ex) { } } } private class PooledThread extends Thread { public PooledThread() { super(ThreadPool.this, "Zigzag_Thread-" + (threadID++)); } public void run() { while (!isInterrupted()) { // get a task to run Runnable task = null; try { task = getTask(); } catch (InterruptedException ex) { } // if getTask() returned null or was interrupted, // close this thread by returning. if (task == null) { return; } // run the task, and eat any exceptions it throws try { task.run(); } catch (Throwable t) { uncaughtException(this, t); } } } }} And next we have the client program defined to ask the ThreadPool to create a group of PooledThreads to run many more tasks. public class OutOfThread { public static void main(String[] args) { if (args.length != 2) { System.out.println("Tests the ThreadPool task."); System.out.println("Usage: java OutOfThread numTasks numThreads"); System.out.println(" numTasks - integer: number of task to run."); System.out.println(" numThreads - integer: number of threads in the thread pool."); return; } int numTasks = Integer.parseInt(args[0]); int numThreads = Integer.parseInt(args[1]); // create the thread pool ThreadPool threadPool = new ThreadPool(numThreads); // run example tasks for (int i = 0; i < numTasks; i++) { threadPool.runTask(createTask(i)); } // close the pool and wait for all tasks to finish. threadPool.join(); } /** * Creates a simple Runnable that prints an ID, waits a long time, then * prints the ID again. */ private static Runnable createTask(final int taskID) { return new Runnable() { public void run() { System.out.println("Task " + taskID + ": start"); // simulate a long-running task try { int i = 0; while (i<9999999L*2000) i++; } catch (Exception ex) { } System.out.println("Task " + taskID + ": end"); } }; }} We ask the ThreadPool to create only 3 threads in its pool, and these 3 threads will run 5 tasks. The expected result could be: Task 2: startTask 0: startTask 1: startTask 0: endTask 3: startTask 2: endTask 4: startTask 1: endTask 3: endTask 4: end However the actual result is: E:\>java -classpath . zigzag.research.threaddump.OutOfThread 5 3Task 2: startTask 0: startTask 1: startTask 2: endTask 3: start In a long period, we find Task 4 are not started yet and the client program is continuously running and waiting, poor performance occurs. Thread Dump Analysis In the Thread Dump we find below information, Zigzag_Thread-3, Zigzag_Thread-1 and Zigzag_Thread-0 are "RUNNABLE". Definitely you cannot find any problem because they are running. After you take several other Thread Dump, it still show these 3 threads running. Zigzag_Thread-2 does not show because it is done. But where is the expected Zigzag_Thread-4? Zigzag_Thread-4 is still waiting in the queue and cannot get any idle thread. "Zigzag_Thread-3" prio=6 tid=0x00000000069d5800 nid=0x26b4 runnable [0x000000000744f000] java.lang.Thread.State: RUNNABLE at zigzag.research.threaddump.OutOfThread$1.run(OutOfThread.java:45) at zigzag.research.threaddump.ThreadPool$PooledThread.run(OutOfThread.java:183)"Zigzag_Thread-1" prio=6 tid=0x00000000069d5000 nid=0x11b4 runnable [0x000000000734f000] java.lang.Thread.State: RUNNABLE at zigzag.research.threaddump.OutOfThread$1.run(OutOfThread.java:45) at zigzag.research.threaddump.ThreadPool$PooledThread.run(OutOfThread.java:183)"Zigzag_Thread-0" prio=6 tid=0x00000000069d2000 nid=0x1a34 runnable [0x000000000724f000] java.lang.Thread.State: RUNNABLE at zigzag.research.threaddump.OutOfThread$1.run(OutOfThread.java:46) at zigzag.research.threaddump.ThreadPool$PooledThread.run(OutOfThread.java:183) Modern Application Server (Web Container) has the interface for user to monitor and modify the Thread Pool size. For example below is Oracle Application Server and Weblogic Server.

Out Of Thread means there are many tasks (requests) are pending to run, but only a few Threads are available. Many queued tasks have to wait for running threads to be idle. It is a much...

Analysis against JVM Thread Dump - Resource Contention

Most of performance issues analyzed against Thread Dump is Resource Contention. That is to say, two or more threads are fighting for the same resource (java object) to ensure they are capable to run, however the resource (java object) is being locked/consumed by another thread and fails to be released in a given time. The keyword in the Thread Dump is "waiting for monitor entry". You may say why it is not "BLOCKED", I will tell you the true in future demonstration. DemonstrationIn this article, let us imagine there are three boys sharing the same bread (the only bread), but each time only ONE boy is allowed to eat the partial and the next boy has to wait until the previous one is done. Here the bread is the shared resource/object and we define it as static in java code. static Object bread = new Object(); As the current boy requires a few time to chew the bread and no others are allowed to share concurrently, we use synchronized function to lock the bread like below. synchronized (bread) { // eating for a few time} All the boys are line up to share the bread one by one, we organize them into three Thread. So the whole source code is clearly presented below. Run the standalone program and monitor the result, will see "boy 1" consumes much time and the other queued "boy 2" and "boy 3" have no chance to share the bread yet. That is because the resource is locked by "boy 1" and not released. E:\>java -classpath . zigzag.research.threaddump.ResourceContentioncurrent boy: 1current boy: 2current boy: 3The boy 1 is eating the bread Such result will bring bad performance to application. Analysis Check the Thread Dump and as I stated above the keyword is "waiting for monitor entry". We easily find out that Thread-2 and Thread-1 are "waiting to lock 0x00000007d663a6d0", and "0x00000007d663a6d0" is actually the bread object and it is locked by Thread-0 (locked 0x00000007d663a6d0). So there is nothing to explain more, your duty is to dive into Thread-0 to check the root cause right now. Nested Contention A common typical case is that many threads are waiting for thread A to release its resource, and thread A however, is waiting for thread B to release B's resource. I call it nested resource contention. In this case, we shall not be treated by the pretension, instead, put your focus on the REAL root cause B, not A.

Most of performance issues analyzed against Thread Dump is Resource Contention. That is to say, two or more threads are fighting for the same resource (java object) to ensure they are capable to run,...

Analysis against JVM Thread Dump - Dead Lock

Dead Lock is a special error need to be avoided in multi-thread programming. It seldom introduces CPU high usage issue, however the most common phenomenon is that the system hangs and has no response to end user. Absolutely it is quite easy to be identified from JVM Thread Dump. In Oracle Agile PLM, I seldom see such issue except one case several years ago that I cannot remind. I will not explain the principle of Dead Lock, which you can google everywhere. I will only describe how to analyze in JVM thread dump from below sample source code and thread dump. Demonstration Thread 0 tries to lock object x before it processes y, while Thread 1 tries to lock object y before it does to x. Each holds the resource which is expected by peer. public class DeadLock implements Runnable { public int i = 1; static Object x = new Object(), y = new Object(); public void run() { System.out.println("current thread=" + i); if (i == 0) { synchronized (x) { try { Thread.sleep(500); } catch (Exception e) { } synchronized (y) { System.out.println("locked y"); } } } if (i == 1) { synchronized (y) { try { Thread.sleep(500); } catch (Exception e) { } synchronized (x) { System.out.println("locked x"); } } } } public static void main(String[] args) { DeadLock test0 = new DeadLock(); DeadLock test1 = new DeadLock(); test0.i = 0; test1.i = 1; Thread t1 = new Thread(test0); Thread t2 = new Thread(test1); t1.start(); t2.start(); }} The program never ends to print the expected line ("locked x" and "locked y") like below, Analysis How the JVM thread dump reflect this behavior. Read the stack we see Thread-1 is waiting to lock 0x00000007d663a588 which represent object y, but 0x00000007d663a588 is already locked by Thread-0 (locked 0x00000007d663a588) . Same thing happens to 0x00000007d663a598 that represents object x. There are a most important keywords "waiting for monitor entry" hint. Pay attention to keyword "BLOCKED". BLOCKED is the thread state result, not root cause, not reason. In many cases "BLOCKED" does not reflect any harmful thread. I may introduce more scenario in future if possible. If we read through the whole thread dump we will see JVM smartly detects the Dead Lock by itself as below, which explain the same I stated above. Note Dead Lock usually does not introduce CPU high usage issue Dead Lock usually makes system unresponsive Never rely on "BLOCKED" if to analyze JVM Thread Dump

Dead Lock is a special error need to be avoided in multi-thread programming. It seldom introduces CPU high usage issue, however the most common phenomenon is that the system hangs and has no...

Analysis against JVM Thread Dump - CPU High Usage Issue

One important duty of mine during years' of technical support is to analyze JVM thread dump, which is an interesting research to help understand how the logic in source code works and more important is that you will learn how to design a good Java EE applications to avoid scenario of high CPU usage, resource contention or something like these. One typical bad design it dead lock, though it seldom happens in modern Java development, but I will demonstrate all of them with sample source code, and show how to analyze the root cause from Thread Dump. All will be practical with no abstract theory. This article will discuss one scenario you will always see, High CPU Usage like 99% or 100%. This case is quite easy to identify the root cause. Let's read this sample source code first, it is just an example and nobody developer would code such. package zigzag.research.threaddump;public class HighCPU { public static void main(String[] args) { while(true){ // do nothing } }} On Linux Run it and top command shows you the problematic parent process (attention it is process in Linux, not thread). Now we look at the JVM thread dump (I removed some unusual information section that I do not care). What problematic thread will you get from below? WAITING status? No! In Java VM, there are many daemon threads and in most of time they are WAITING. "Finalizer" daemon prio=1 tid=0x08fb6428 nid=0xbea in Object.wait() [0xb59d5000..0xb59d60b0] at java.lang.Object.wait(Native Method) - waiting on(a java.lang.ref.ReferenceQueue$Lock) at java.lang.ref.ReferenceQueue.remove(ReferenceQueue.java:116) - locked(a java.lang.ref.ReferenceQueue$Lock) at java.lang.ref.ReferenceQueue.remove(ReferenceQueue.java:132) at java.lang.ref.Finalizer$FinalizerThread.run(Finalizer.java:159)"Reference Handler" daemon prio=1 tid=0x08fb5ea8 nid=0xbe9 in Object.wait() [0xb5a56000..0xb5a56e30] at java.lang.Object.wait(Native Method) - waiting on(a java.lang.ref.Reference$Lock) at java.lang.Object.wait(Object.java:474) at java.lang.ref.Reference$ReferenceHandler.run(Reference.java:116) - locked(a java.lang.ref.Reference$Lock)"main" prio=1 tid=0x08f128d0 nid=0xbe5 runnable [0xff80c000..0xff80c5e8] at zigzag.research.threaddump.HighCPU.main(HighCPU.java:6) On Linux, we can press Shift-H on top command and the individual Threads will be displayed instead of parent PID. The man page for top command is: -H : Threads toggle Starts top with the last remembered 'H' state reversed. When this toggle is On, all individual threads will be displayed. Otherwise, top displays a summation of all threads in a process. Now we get above top command screenshot with Shift-H is toggled on. The problematic Thread (PID) occurs as 3045 which is Decimal and the Hexadecimal value is 0xBE5. So you got it? "main" prio=1 tid=0x08f128d0 nid=0xbe5 runnable [0xff80c000..0xff80c5e8] at zigzag.research.threaddump.HighCPU.main(HighCPU.java:6) On Windows It was difficult to get the OS level Thread on Windows platform to anlyze thread dump. Luckily we have 3rd part tool like Process Explorer to get the problematic TID like below Screenshot. You will see the CPU usage increase to almost 100% if your computer CPU is single core. In my example it consumes 24.71% because mine is 4 core CPU. As above screenshot shows a problematic TID which takes 24.71% CPU time and never goes down, which is abnormal. Here the TID is native thread id in operation system level and it is not the "tid" of JVM thread dump, they are definitely different. The TID in OS is the nid (Native Thread ID) in JVM Thread Dump stack. In this example the TID (Native Thread ID) is 2708 (Decimal), which is 0xA94(hexadecimal). So you will get the real problematic thread stack if you have: "main" prio=6 tid=0x000000000054b000 nid=0xa94 runnable [0x00000000025cf000] java.lang.Thread.State: RUNNABLE at zigzag.research.threaddump.HighCPU.main(HighCPU.java:6) Summary Java nid(Native Thread ID) = Linux Process = Windows Thread

One important duty of mine during years' of technical support is to analyze JVM thread dump, which is an interesting research to help understand how the logic in source code works and more important...

Into Agile SDK Class Loader Logic

When you use Agile API to develop SDK customization to extend your functionality, you may not care about how SDK architecture is designed by Agile smart engineers and you only focus attention on your own code. As the software engineer and Agile implementation developer, it's better to understand SDK internal, which could benefit you for your development/design ability on Java. Be realistic, you will understand how to diagnose SDK issues. This article will discuss how SDK dynamically load required classes from remote and reuse them, and how Agile loads the PX jars dynamically at server side. SDK Client Class Loading in Client MachineFirst we look at this quite simple code which only creates Agile session. public class TestClient {public static void main(String args[]){String url = "http://agile.company.com/Agile"; try { Map params = new HashMap(); String stLoginUser = "admin"; String stLoginPW = "agile"; long start = System.currentTimeMillis();AgileSessionFactory factory = AgileSessionFactory.getInstance(url); params.put(AgileSessionFactory.USERNAME, stLoginUser); params.put(AgileSessionFactory.PASSWORD, stLoginPW); IAgileSession session = factory.createSession(params); System.out.print((System.currentTimeMillis()-start )+ " milliseconds"); } catch (APIException e) {e.printStackTrace();} }}We use System.currentTimeMillis() to record the elapsed time and we check two-time execution. //First time25422 milliseconds//Second time12765 millisecondsYou may feel interested that it must be something cached in SDK client local that the second execution benefits from. It is and how to identify. I will show two important JVM parameters Agile uses. -Dncl.printload=true -Dncl.printfind=trueActually there is another parameter -Dncl.invalidate=true, which force Agile to load classes every time the SDK is executed and no cache is used."ncl" means Network Class Loader. Never mind let us just go ahead to apply them to SDK client's JVM and run it again (You are required to delete java.io.tmpdir/AgileSDK.cache/ folder first, I will discuss later).Now you will see a mass of messages printed in SDK Client console Loading 'com.agile.api.pc.Session' ... loaded (total bytes: 41406)Loading 'com.agile.api.common.IResourceBundleHolder' ... loaded (total bytes: 41609)Loading 'com.agile.api.pc.APIObject' ... loaded (total bytes: 48867)Loading 'com.agile.api.common.IObjectType' ... loaded (total bytes: 50735)Loading 'com.agile.api.common.ISecuredObject' ... loaded (total bytes: 51138)... ...... ...Run the client program for the second time and notice that Loading 'com.agile.api.pc.Session' ... loaded from cacheLoading 'com.agile.api.common.IResourceBundleHolder' ... loaded from cacheLoading 'com.agile.api.pc.APIObject' ... loaded from cacheLoading 'com.agile.api.common.IObjectType' ... loaded from cacheLoading 'com.agile.api.common.ISecuredObject' ... loaded from cache... ...... ...We see difference that the first time it shows "loaded" and second time it shows "loaded from cache". What is loaded?Of course something is loaded, but what is that? Let's check HTTP access log. (Why check HTTP log, well, it is a secret). 127.0.0.1 - - [27/Mar/2013:22:57:40 -0700] "GET /Agile/ServerAPIProperties HTTP/1.1" 200 792 127.0.0.1 - - [27/Mar/2013:22:57:40 -0700] "GET /Agile/LoaderServlet?op=loadClass&val=com%2Fagile%2Fapi%2Fpc%2FSession.class HTTP/1.1" 200 41494 127.0.0.1 - - [27/Mar/2013:22:57:40 -0700] "GET /Agile/LoaderServlet?op=loadClass&val=com%2Fagile%2Fapi%2Fcommon%2FIResourceBundleHolder.class HTTP/1.1" 200 211 127.0.0.1 - - [27/Mar/2013:22:57:40 -0700] "GET /Agile/LoaderServlet?op=loadClass&val=com%2Fagile%2Fapi%2Fpc%2FAPIObject.class HTTP/1.1" 200 7274 127.0.0.1 - - [27/Mar/2013:22:57:40 -0700] "GET /Agile/LoaderServlet?op=loadClass&val=com%2Fagile%2Fapi%2Fcommon%2FIObjectType.class HTTP/1.1" 200 1876 ... ...... ...There are many HTTP access record in log file and actually, we can access them in browser and browser will download these files for us.http://agile.company.com/Agile/LoaderServlet?op=loadClass&val=com%2Fagile%2Fapi%2Fpc%2FSession.class Save above to Session.class, and the filesize is 41406 http://agile.company.com//Agile/LoaderServlet?op=loadClass&val=com%2Fagile%2Fapi%2Fcommon%2FIResourceBundleHolder.class Save above to IResourceBundleHolder.class and the filesize is 203Where is loaded?Ok, we get it that these classes are loaded and saved to local. But where are they? Agile saves all loaded classes to SDK client machine at this location: System.getProperty("java.io.tmpdir") + "/AgileSDK.cache/"If you open this location you will find there are two files: #host_port#_Agile_.cache : It contains all the downloaded classes and combined to a binary file. #host_port#_Agile_.properties : It contains the indexing data for the classes(File size and file header position locator)Let's open_Agile_.properties, and check below: current-impl-version=Agile PLM 9.3.1.1 (2011-04-20.15-14-39.966)current-server-version=9.3.1.1 (Build 43)SIZ-com/agile/api/pc/Session.class=41406IDX-com/agile/api/pc/Session.class=0SIZ-com/agile/api/common/IResourceBundleHolder.class=203IDX-com/agile/api/common/IResourceBundleHolder.class=41406SIZ-com/agile/api/pc/APIObject.class=7258IDX-com/agile/api/pc/APIObject.class=41609SIZ means the class filesize. IDX is the class position locator in_Agile_.cache, that is to tell Agile where the current class begins from. When is loaded?Agile will cache all the loaded classes into one single *.cache file for the first time. And next executions will load from local directly. But in client code, which class invocation will make Agile to load(from local or remote)? Exactly during this execution: IAgileSession session = factory.createSession(params); How is loaded?Agile use a Network Class Loader which implements Java ClassLoader to load com/agile/api/pc/Session.class first, then get all other classes from the same ClassLoader which initializes Session.class's in server's JVM, not client's. PX Class Loading in ServerYou are always asked by Oracle Support to check this url: http://agile.company.com/Agile/ServerAPIProperties . Below is sample output from the link: ## java.io.tmpdir=C:\Windows\TEMP\# java.io.tmpdir.readable=true# java.io.tmpdir.writable=true# sdk.extensions=C:/Agile/Agile931/integration/sdk/extensions# sdk.extensions.readable=true# sdk.extensions.writable=true# cookie.domain=.sl.agilesoft.com#minimum-api-version=9.22current-server-version=9.3.1.1 (Build 43)current-impl-version=Agile PLM 9.3.1.1 (2011-04-20.15-14-39.966)session-class=com.agile.api.pc.Sessionauthenticator-class=com.agile.api.common.WebLogicAuthenticatortransaction-manager=com.agile.api.common.WebLogicTransactionManagerapp.server.type=weblogicenv-name.0=java.naming.factory.initialenv-name.1=java.naming.provider.urlenv-value.1=t3://agile.company.com:80env-set.1=falseenv-value.0=weblogic.jndi.WLInitialContextFactoryenv-set.0=falseThere are two important items you need to care about: java.io.tmpdir and sdk.extensions. All the PX jars are deployed to sdk.extensions folder, but why we need to care about java.io.tmpdir? Check the folder and you would see a folder name "sdk.extensions.libs", and there are all the PX jars here. Why these jars are copied from sdk.extensions to java.io.tmpdir\sdk.extensions.libs\ ?Agile first copies them to java.io.tmpdir\sdk.extensions.libs\ folder, then uses URL Class Loader to dynamically load all the classes/jars in this temporary folder upon below event: When PX is invoked for the first time and no such jar in sdk.extensions.libs folder When the timestamp of same Jar in sdk.extensions is newer than the one in sdk.extensions.libs and PX executes again When PX is setup in JavaClient for the first time Error of Local Class Incompatible: Stream Classdesc SerialVersionUIDYou may see "Error of Local Class Incompatible: Stream Classdesc SerialVersionUID" very often especially after you upgrade Agile with main release or patch/hotfix. It means the version of loaded class in Client is different from version in server side. You should check current-impl-version and current-server-version in below two items then. #host_port#_Agile_.properties in client's "java.io.tmpdir/AgileSDK.cache/" folder http://agile.company.com/Agile/ServerAPIProperties

When you use Agile API to develop SDK customization to extend your functionality, you may not care about how SDK architecture is designed by Agile smart engineers and you only focus attention on your...