Showing posts with label Programming Tips. Show all posts
Showing posts with label Programming Tips. Show all posts

Thursday, October 13, 2011

Making sure XBRL kissing and not kicking you!




During the course of my professional career, may it be in business or information technology, I have seen and heard innumerable hypes and promises of disrupting standards, paradigms, solutions and technology advancements. Usually, mega-vendors with plentiful of marketing capital will succeed in planting seeds to gain buy-in and yet losing momentum when the trend reaching a critical mass of demystification and finally the bubble busted and they move on to another vicious cycle of hype-high-haunt. History is likely to repeat itself and what businesses can do to protect their valuable capitals is to put in the right perspective and context in understanding the impact and risk in adopting certain approach to meet competitive advantage demand, market pressures, regulatory (BNM NSRS ISS) requirements and et cetera.


Life’s tough but that’s life.





I would like to focus the spotlight in this post to be pertinent about Extensible Business Reporting Language, or commonly known as XBRL. I think I will most likely skip all the boilerplate explanation and description of what XBRL is and some common sense facts about it that you and I can reason without any significant efforts. I am more interested in shedding light about best practices, prevention measures, implementation management and techniques for making sure proper alignments between domains of business, data, application and technology.


To make this even more interesting, I am also doing some thoughts about topics such as BPO model to XBRL, the problem of agility mismatch and other matters.


Just close your eyes and imagine for a while the ideal state of anything that is perfectly blended into our life. How would we perceive them? The ideal case is that we don’t even need to know about their existence since they are so well blended into our daily routines or activities; we just take them for granted. For instance, we don’t need to know how an automobile’s engine works in order to drive a car. We can study about them if we have the luxury of time and resources, and at our will.


Same scenario is here for XBRL. The current proliferation of standalone XBRL conversion tool demands understanding of intricacies of XBRL to fully harness the real benefits of XBRL. The fact that these tools are standalone, single purpose built applications meant the chasms to enable seamless information flow is predicted to be more challenging than a proper solution with a long term interoperability vision. Information management in XBRL can be chaotic without built-in functionalities within the tools to perform auditing, archiving, versioning, automated and human workflow process for validation. The business might ends up with a mess of thousands of scattered XBRL taxonomies and instances with no tractable ways to consolidate them.


Have something better and let standalone XBRL conversion tool be the last resort.




My point is clear and obvious, a standalone tool could be suffice for relatively small company with human manageable size and complexity of XBRL artefacts, however it is not suitable for large corporations such as financial institutions, conglomerates and federal agencies. Operational expenditure of using standalone XBRL tool is forecasted to be exceedingly high compared to an enterprise grade XBRL solution. Next is the impact to the corporation’s evolve-ability and maturity level in enterprise data management, especially when XBRL artefacts are not meant to be the end, but only a means to the standardized information sharing platform. This is one critical area where technological advancement is needed to accelerate the adoption of XBRL because it is virtually hard to find commercial data integration tools that can natively process XBRL format. At the moment, these tools can only parse and understand XML format. XML and XBRL are not the same, we all should know by now right? One of the database vendors is coming up with a database vault that can process XBRL natively; however we are yet to see their releases and feature sets.
Aim for centralized management and governance model.

The sole action of buying a standalone XBRL conversion tool without a much higher level enterprise planning process is like buying and driving a car in a poor city planning area where you have no idea whether there is proper road in front of you. Still, the car will move and you might eventually reach your destination, well, predictably in a stochastic and risky way. The bottom line to use a standalone XBRL conversion toll is that even if you have decided to adopt the tool, please request your vendor or internal governance team to come up with a comprehensive strategy and future transitioning plan for it.



Prioritize and focus on the business values.




Avoid been inundated with XBRL related jargons and terminology because a well-planned business application using XBRL shall abstract most of these technicalities from business users, even power users. I know the in-and-out of XBRL because I spent quite a fair amount of time in understanding their specifications and business orientation. At the basic, XBRL got at least 3 specifications to be understood plus all the related XML related specifications; I bet my clients won’t have the luxury of resources to skimp them thoroughly. On top of everything, there are global standards such as IFRS, US-GAAP, GRI and many more to be adhered to govern a business. So, when possible, procure a complete business solution that designed at the proper level of technical abstractions.


Know your opponent’s agility.

In a regulatory landscape where regulator (e.g. BNM NSRS ISS) requires monitoring and compliance submission information from regulated entities, these entities should know the capability and agility of their regulator to pick the right XBRL solution. Let me explain the idea in more details. You see, regulator is implementing XBRL solution for say regular financial statement submissions from regulated entities. There are valid rationales and implications of why they do so. One of them is to progressively increase their capability of requesting and consuming more detailed, more frequent and more accurate information and catch up with regional and global peer pressures in adopting evolving industry standards.


Once the regulator possesses this agility, regulated entities will face what I coined the problem of agility mismatch, if they didn’t level up in this playing field. To meet the initial regulatory and compliance requirements mandated by regulator using the new XBRL platform is relatively simple for basically whichever approach that regulated entities adopted, may it be standalone XBRL tool, and enterprise integrated solution or even pure human solution. The real issue is the size, complexity and interval of future changes that regulated entities faced. Regulated entities most likely will caught in a downward spiral of catching up with the reporting changes. Money can be spent better elsewhere don’t you think so?


XBRL is at the centre of information nexus.

It is very ideal indeed to position XBRL like this and I think is risky because supporting tools, standards and technologies are not there yet to reinforce the implementation. Nonetheless, XBRL can really be used to supplement the organization’s effort in consolidating master data, reference data, metadata and their governance initiatives. Don’t lock the value of XBRL in a silo. XBRL is significantly much better than any ad-hoc and in-house standardization efforts such as creating a XML based data sharing format, standardized document structures and tons of other such things you and I have seen. XBRL is developed to solve many of these repeating and known problems. Why not leverages XBRL but reinvents this ugly wheel?


Lastly of this post, I don’t favour the idea of engaging a BPO vendor to take care of the organization needs to be transited to the paradigm where XBRL is used for regulatory and compliance requirements. I think XBRL is here to stay and getting more ubiquitous, pervasive and critical for businesses to stay relevant, competitive and agile. XBRL can be a socio-technical object transforming the way we handle business information.


I will write more of my thoughts out in the future about this topic. Pen down.






Sunday, August 31, 2008

J2EE EAR File Structure



A diagram from IBM Websphere redbook. I just keep it here to remind my rusted high performance brain.




Thursday, August 28, 2008

JNDI Application Client in WAS 6.1 - Part 2




Continued from my previous post, this post attempts to address some of technical intricacies that you might face when try to authenticate yourself outside Java EE containers when doing naming operations.

Previously, I simply assigned the necessary rights to EVERYONE in Websphere Application Server V6.1 Administrative Console to enable anyone (Including those Unauthenticated) to perform naming operations.

However, this setting is not appropriate in production environment because some operations such as removing bindings and create new bindings are considered as privileged operations that require thoughtful considerations.

Assuming that you are created a new WAS User named "NamingUser1" and assigned this user with relevant rights (i.e. CosNaming Delete, etc)

Now the trick is to pass these credentials to WAS from the Java program.

So, the first mistake that might happened is you assumed the following codes will work:


Hashtable env = new Hashtable();

env.put(Context.PROVIDER_URL, "corbaloc:iiop:localhost:2810/NameService");
env.put(Context.INITIAL_CONTEXT_FACTORY
,"com.ibm.websphere.naming.WsnInitialContextFactory");

env.put(Context.SECURITY_PRINCIPAL, "NamingUser1");
env.put(Context.SECURITY_CREDENTIALS, "password1");



No, this will not work. You will shoot by the below exception:


javax.naming.NoPermissionException: NO_PERMISSION exception caught [Root exception is org.omg.CORBA.NO_PERMISSION:
>> SERVER (id=11c328fe, host=eddy) TRACE START:
>> org.omg.CORBA.NO_PERMISSION: Caught WSSecurityContextException in WSSecurityContext.acceptSecContext(), reason: Major Code[0] Minor Code[0] Message[ null] vmcid: 0x49424000 minor code: 300 completed: No
>> at com.ibm.ISecurityLocalObjectBaseL13Impl.PrincipalAuthFailReason.map_auth_fail_to_minor_code(PrincipalAuthFailReason.java:83)
>> at com.ibm.ISecurityLocalObjectBaseL13Impl.CSIServerRIBase.authenticateSecurityTokens(CSIServerRIBase.java:2575)
>> at com.ibm.ISecurityLocalObjectBaseL13Impl.CSIServerRI.receive_request(CSIServerRI.java:485)
>> at com.ibm.rmi.pi.InterceptorManager.invokeInterceptor(InterceptorManager.java:592)
>> at com.ibm.rmi.pi.InterceptorManager.iterateServerInterceptors(InterceptorManager.java:507)
>> at com.ibm.rmi.pi.InterceptorManager.iterateReceiveRequest(InterceptorManager.java:738)
>> at com.ibm.CORBA.iiop.ServerDelegate.dispatchInvokeHandler(ServerDelegate.java:602)

...
...



You need to use JAAS to perform the authentication.

To make the case clearer, let us focus on the following source codes:


package lab.namespace;

import java.rmi.RMISecurityManager;
import java.security.PrivilegedAction;
import java.util.Hashtable;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.security.auth.login.LoginContext;

import com.ibm.ejs.models.base.bindings.applicationbnd.Subject;
import com.ibm.websphere.naming.PROPS;
import com.ibm.websphere.security.auth.WSSubject;
import com.ibm.websphere.security.auth.callback.WSCallbackHandlerImpl;
import com.ibm.ws.security.auth.callback.WSCallbackHandler;

public class Connect {
public static void main(String[] args) throws Exception {
Hashtable env = new Hashtable();
env.put(Context.PROVIDER_URL, "corbaloc:iiop:localhost:2810/NameService");
env.put(Context.INITIAL_CONTEXT_FACTORY,"com.ibm.websphere.naming.WsnInitialContextFactory");
final Context initialContext = new InitialContext(env);
initialContext.lookup("");

LoginContext loginContext =
new LoginContext("WSLogin",new WSCallbackHandlerImpl("NamingUser1","password1"));

loginContext.login();

javax.security.auth.Subject s = loginContext.getSubject();

WSSubject.doAs(s, new PrivilegedAction(){
public Object run() {
try{
initialContext.bind("hello", "1234");
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
});

System.out.println(loginContext.getSubject());

}

}



You also need to add the following JARs to the class path:


${WAS_INSTALLED_FOLDER}/com.ibm.ws.webservices.thinclient_6.1.0.jar


Then you crossed your finger and execute the program again. And yet it still fails.


Exception in thread "P=172625:O=0:CT" java.lang.SecurityException: Unable to locate a login configuration
at com.ibm.security.auth.login.ConfigFile.(ConfigFile.java:129)
at java.lang.Class.newInstanceImpl(Native Method)
at java.lang.Class.newInstance(Class.java:1263)
at javax.security.auth.login.Configuration$3.run(Configuration.java:239)
at java.security.AccessController.doPrivileged(AccessController.java:241)
at javax.security.auth.login.Configuration.getConfiguration(Configuration.java:233)
at javax.security.auth.login.LoginContext$1.run(LoginContext.java:260)
at java.security.AccessController.doPrivileged(AccessController.java:192)
at javax.security.auth.login.LoginContext.init(LoginContext.java:257)
at javax.security.auth.login.LoginContext.(LoginContext.java:426)
at lab.namespace.Connect.main(Connect.java:38)
Caused by: java.io.IOException: Unable to locate a login configuration
at com.ibm.security.auth.login.ConfigFile.init(ConfigFile.java:238)
at com.ibm.security.auth.login.ConfigFile.(ConfigFile.java:127)
... 10 more


Now you shall setup JAAS specific environment.

Copy the following files to your workspace:


${PROFILE_HOME}\properties\sas.client.props
${PROFILE_HOME}\properties\sas.client.props
${PROFILE_HOME}\properties\wsjaas.client.conf


Modify the sas.client.props


com.ibm.CORBA.validateBasicAuth=false
com.ibm.CORBA.securityServerHost=localhost
com.ibm.CORBA.securityServerPort=2809
com.ibm.CORBA.loginSource=none
com.ibm.CORBA.loginUserid=NamingUser1
com.ibm.CORBA.loginPassword=password1


Note: Here I assume that the bootstrap port is 2809.


Modify the ssl.client.props


user.root=C:/IBM/WebSphere/ND/profiles/AppSvr01
com.ibm.ssl.keyStore=C:/IBM/WebSphere/ND/profiles/AppSvr01/etc/key.p12
com.ibm.ssl.trustStore=C:/IBM/WebSphere/ND/profiles/AppSvr01/etc/trust.p12


Note: Here I just used the same keystore from the server. It might not be the case for production environment.

You will also need to modify the source code to include the following line:


System.setSecurityManager(new RMISecurityManager());


Create one new file named "security.policy" and specify the following in it.


grant {
permission java.security.AllPermission;
};


Note: This is just for demostration purposes. You should tune the security policy instead.

Lastly you must add few JVM arguments for execution.


-Djava.security.auth.login.config=${YOUR_PATH}\wsjaas.conf
-Dcom.ibm.CORBA.ConfigURL=${YOUR_PATH}\sas.client.props
-Djava.security.policy=${YOUR_PATH}\security.policy
-Dcom.ibm.SSL.ConfigURL=file:${YOUR_PATH}\ssl.client.props



Potential Mistake #2: JVM argument "com.ibm.SSL.ConfigURL"

The value specified for this argument must start with "file:" for file URL. Fail to do this will make your head spin.


Finally, the program will be successfully executed and the new String object is bound to the name space. You can use dumpNameSpace utility to verify this.


Good luck.



JNDI Application Client in WAS 6.1




If you deployed your application into the web container or EJB container of the J2
EE/Java EE application server and you try to perform naming operations, basically the container already setup all necessary environment configuration for you to obtain the initial context. Initial context basically is the starting point in the name space that you want to manipulate. In Websphere, an initial context can be treated as a connection to the name server where the connection is defined by the bootstrap host, bootstrap port and protocol as part of the provider URL.

In the event that you need to develop application client that runs outside the containers, then you shall need to configure this connection yourself or as part of the application assembly process assisted by the tool. AST and Rational Application Developer can do all these dirty works for you.

However, under the circumstances that you don't have the luxury of using these tools, then you really need to know what to do.

In this example, I'm using IBM Websphere Application Server V6.1 with FP0.

Sample code:


package lab.namespace;

import java.util.Hashtable;
import javax.naming.Context;
import javax.naming.InitialContext;

public class Connect {

public static void main(String[] args) throws Exception {

Hashtable env = new Hashtable();
env.put(Context.PROVIDER_URL, "corbaloc:iiop:localhost:9809");
env.put(Context.INITIAL_CONTEXT_FACTORY,"com.ibm.websphere.naming.WsnInitialContextFactory");

Context initialContext = new InitialContext(env);
Context myCtx = (Context)initialContext.lookup("cell/persistent");
myCtx.bind("hello", "123");


}


Without additional information, when you execute this program, you shall hit the first error:


Exception in thread "main" javax.naming.NoInitialContextException: Cannot instantiate class: com.ibm.websphere.naming.WsnInitialContextFactory [Root exception is java.lang.ClassNotFoundException: com.ibm.websphere.naming.WsnInitialContextFactory]
at javax.naming.spi.NamingManager.getInitialContext(NamingManager.java:669)
at javax.naming.InitialContext.getDefaultInitCtx(InitialContext.java:259)
at javax.naming.InitialContext.init(InitialContext.java:235)
at javax.naming.InitialContext.(InitialContext.java:209)
at lab.namespace.Connect.main(Connect.java:19)
Caused by: java.lang.ClassNotFoundException: com.ibm.websphere.naming.WsnInitialContextFactory
at java.lang.Class.forName(Class.java:164)
at com.sun.naming.internal.VersionHelper12.loadClass(VersionHelper12.java:57)
at javax.naming.spi.NamingManager.getInitialContext(NamingManager.java:666)
... 4 more


You can solve this by adding the appropriate JAR into your class path. Locate ws_runtimes.jar at


${your_was_installed_dir}\deploytool\itp\plugins\com.ibm.websphere.v61_6.1.0\ws_runtimes.jar


You can try to execute again, and this time possibly you will hit the second error:


Exception in thread "main" java.lang.NoClassDefFoundError: com/ibm/CORBA/iiop/ObjectURL
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Unknown Source)
at com.ibm.websphere.naming.WsnInitialContextFactory.init_implClassCtor(WsnInitialContextFactory.java:172)
at com.ibm.websphere.naming.WsnInitialContextFactory.getInitialContext(WsnInitialContextFactory.java:112)
at javax.naming.spi.NamingManager.getInitialContext(Unknown Source)
at javax.naming.InitialContext.getDefaultInitCtx(Unknown Source)
at javax.naming.InitialContext.init(Unknown Source)
at javax.naming.InitialContext.(Unknown Source)
at lab.namespace.Connect.main(Connect.java:19)


The stack trace pointed out that the program need more JARs.

Here you have 2 choices as solution to the problem.

1. Change your JRE from SUN to IBM

To do this in Eclipse, go to Windows->Preferences->Java->Installed JREs

And specify the location of IBM JRE


${your_was_installed_dir}\java\jre\


And set this JRE as default JRE.

2. Add in only the specific JARs in your class path

Locate the following JARs:


${your_was_installed_dir}\java\jre\lib\ibmorb.jar
${your_was_installed_dir}\java\jre\lib\ibmorbapi.jar


High chance is that you still encounter another error after you added the above JARs.


WARNING: jndiNamingException
Exception in thread "P=762318:O=0:CT" javax.naming.NoPermissionException: NO_PERMISSION exception caught [Root exception is org.omg.CORBA.NO_PERMISSION:
>> SERVER (id=144d42ac, host=eddy) TRACE START:
>> org.omg.CORBA.NO_PERMISSION: Not authorized to perform bind_java_object operation. vmcid: 0x0 minor code: 0 completed: No
>> at com.ibm.ws.naming.cosbase.WsnOptimizedNamingImplBase.performAuthorizationCheck(WsnOptimizedNamingImplBase.java:4745)
>> at com.ibm.ws.naming.cosbase.WsnOptimizedNamingImplBase.bind_java_object(WsnOptimizedNamingImplBase.java:1267)
>> at com.ibm.WsnOptimizedNaming._NamingContextImplBase._invoke(_NamingContextImplBase.java:125)
>> at com.ibm.CORBA.iiop.ServerDelegate.dispatchInvokeHandler(ServerDelegate.java:613)
>> at com.ibm.CORBA.iiop.ServerDelegate.dispatch(ServerDelegate.java:466)
>> at com.ibm.rmi.iiop.ORB.process(ORB.java:503)
>> at com.ibm.CORBA.iiop.ORB.process(ORB.java:1552)
>> at com.ibm.rmi.iiop.Connection.respondTo(Connection.java:2673)
>> at com.ibm.rmi.iiop.Connection.doWork(Connection.java:2551)
>> at com.ibm.rmi.iiop.WorkUnitImpl.doWork(WorkUnitImpl.java:62)
>> at com.ibm.ejs.oa.pool.PooledThread.run(ThreadPool.java:95)
>> at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:1498)
>> SERVER (id=144d42ac, host=eddy) TRACE END.
vmcid: 0x0 minor code: 0 completed: No]
at com.ibm.ws.naming.jndicos.CNContextImpl.doBind(CNContextImpl.java:2322)
at com.ibm.ws.naming.jndicos.CNContextImpl.bind(CNContextImpl.java:534)
at lab.namespace.Connect.main(Connect.java:23)
Caused by: org.omg.CORBA.NO_PERMISSION:
>> SERVER (id=144d42ac, host=eddy) TRACE START:
>> org.omg.CORBA.NO_PERMISSION: Not authorized to perform bind_java_object operation. vmcid: 0x0 minor code: 0 completed: No
>> at com.ibm.ws.naming.cosbase.WsnOptimizedNamingImplBase.performAuthorizationCheck(WsnOptimizedNamingImplBase.java:4745)
>> at com.ibm.ws.naming.cosbase.WsnOptimizedNamingImplBase.bind_java_object(WsnOptimizedNamingImplBase.java:1267)
>> at com.ibm.WsnOptimizedNaming._NamingContextImplBase._invoke(_NamingContextImplBase.java:125)
>> at com.ibm.CORBA.iiop.ServerDelegate.dispatchInvokeHandler(ServerDelegate.java:613)
>> at com.ibm.CORBA.iiop.ServerDelegate.dispatch(ServerDelegate.java:466)
>> at com.ibm.rmi.iiop.ORB.process(ORB.java:503)
>> at com.ibm.CORBA.iiop.ORB.process(ORB.java:1552)
>> at com.ibm.rmi.iiop.Connection.respondTo(Connection.java:2673)
>> at com.ibm.rmi.iiop.Connection.doWork(Connection.java:2551)
>> at com.ibm.rmi.iiop.WorkUnitImpl.doWork(WorkUnitImpl.java:62)
>> at com.ibm.ejs.oa.pool.PooledThread.run(ThreadPool.java:95)
>> at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:1498)
>> SERVER (id=144d42ac, host=eddy) TRACE END.
vmcid: 0x0 minor code: 0 completed: No
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:67)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:521)
at com.ibm.rmi.iiop.ReplyMessage._getSystemException(ReplyMessage.java:241)
at com.ibm.rmi.iiop.ReplyMessage.getSystemException(ReplyMessage.java:189)
at com.ibm.rmi.iiop.ClientResponseImpl.getSystemException(ClientResponseImpl.java:232)
at com.ibm.rmi.corba.ClientDelegate.intercept(ClientDelegate.java:982)
at com.ibm.rmi.corba.ClientDelegate.invoke(ClientDelegate.java:459)
at com.ibm.CORBA.iiop.ClientDelegate.invoke(ClientDelegate.java:1150)
at com.ibm.rmi.corba.ClientDelegate.invoke(ClientDelegate.java:778)
at com.ibm.CORBA.iiop.ClientDelegate.invoke(ClientDelegate.java:1180)
at org.omg.CORBA.portable.ObjectImpl._invoke(ObjectImpl.java:484)
at com.ibm.WsnOptimizedNaming._NamingContextStub.bind_java_object(_NamingContextStub.java:174)
at com.ibm.ws.naming.jndicos.CNContextImpl.cosBindJavaObject(CNContextImpl.java:3962)
at com.ibm.ws.naming.jndicos.CNContextImpl.doBind(CNContextImpl.java:2260)
... 2 more


The exception occured due to the fact that WAS V6.1 control the usages of CORBA Naming Service to only those users/groups who is assigned the following rights depending on the action.


Cos Naming Read, Cos Naming Write, Cos Naming Create, Cos Naming Delete


You will need to acess Administrative Console (Integrated Solution Console) to grant the rights.

1. Open Administrative Console
2. Go to Environment -> Naming -> CORBA Naming Service Groups
3. Click on the EVERYONE group (This is just for simplicity, you can use other group)
4. Select the right(s)
5. Save and restart the server.
6. Modify the original program


package lab.namespace;

import java.util.Hashtable;
import javax.naming.Context;
import javax.naming.InitialContext;


public class Connect {

public static void main(String[] args) throws Exception {

Hashtable env = new Hashtable();
env.put(Context.PROVIDER_URL, "corbaloc:iiop:localhost:9809");
env.put(Context.INITIAL_CONTEXT_FACTORY,"com.ibm.websphere.naming.WsnInitialContextFactory");

Context initialContext = new InitialContext(env);
Context myCtx = (Context)initialContext.lookup("cell/persistent");
myCtx.bind("hello", "1234");

}

}


You're done. Good luck.




Top Blogs

Saturday, July 26, 2008

Connecting DB2 LUW to DB2 OS/390, Simple right?



Assuming that every necessary configurations are done at the zO/S, i.e. communication connectivity, authorizations, etc. It is blindly simple to set up DB2 LUW to talk to DB2 OS/390.

You just need to do the following in DB2 Command Window (Let say in Windows).



db2 catalog tcpip node NODE1 REMOTE SERVER OSTYPE OS390

db2 catalog dcs database AS

db2 catalog database AUTHENTICATION DCS



Ok, and if previously the utility packages are not bind yet, you might hit errors when try to run even simple commands in DB2 OS/390. To fix this, You will need to change directory to your \bnd now, and enter the following commands after you are connected to the host:


db2 bind @db2ubind.lst blocking all grant public
db2 bind @db2cli.lst blocking all grant public
db2 bind @ddcsmvs.lst blocking all grant public


Again, I will ask "Simple right?" ;-)




Top Blogs

Thursday, December 06, 2007

Geek! SQL or Java?

Typical hard core (and perhaps inexperience) OO developer will despise others that put business logics in somewhere not their sphere of influences, well, let say in database server in the form of SQL stored procedures or UDF. To a certain extent, me as an overlord in OO realm (:p), agreed on this obstinated design decision, but sometime over engineering an application design will bring only undesirable consequences.

Design is all about trade offs, trade offs and trade offs. There is time when you should capitalize on platform specific features to make your life simpler and meeting the supposed functionalities. Remember the MERGE statement in my previous blog, LOL

Putting logics in database layer have advantages and disadvantages. Especially in this age of messy system integrations and consolidations, more than one application tends to access the same relational data sources.

You should notice by now around you, if the corporate IT environment involves lots of enterprise applications, that exact same logics were replicated across horizontally aligned systems. Changes are foreseen to be challenging.

The good news is you can use your existing knowledge and put it upfront in IBM DB2 environment. Support for Java Stored Procedures and UDFs is there for quite sometime, but their popularity still not exceptionally high, maybe due to the fact that data layer programmers' preference on SQL as prime language.

Basically what you need to do are (For Windows):

1. Put your logic in a Java class' static methods
2. Copy the compiled bytecodes in the specified DB2 directory, depending on whether it is FENCED or UNFENCED (for UDFs). or call a system SP to install the packaged JAR that contains the bytecodes.

3. Issue CREATE FUNCTION/PROCEDURE with proper options to the external Java methods.

That's all. Simple right? Of course NO, there are other critical considerations when using Java SP/UDFs.

Check out the REDBOOK here

Good luck, Folks.

Handle Slowly Changing Dimension in DB2

Excerpt from WWW about Slowly Changing Dimension:



The "Slowly Changing Dimension" problem is a common one particular to data warehousing. In a nutshell, this applies to cases where the attribute for a record varies over time. We give an example below:

Christina is a customer with ABC Inc. She first lived in Chicago, Illinois. So, the original entry in the customer lookup table has the following record:

Customer Key Name State
1001 Christina Illinois


At a later date, she moved to Los Angeles, California on January, 2003. How should ABC Inc. now modify its customer table to reflect this change? This is the "Slowly Changing Dimension" problem.

There are in general three ways to solve this type of problem, and they are categorized as follows:

Type 1: The new record replaces the original record. No trace of the old record exists.

Type 2: A new record is added into the customer dimension table. Therefore, the customer is treated essentially as two people.

Type 3: The original record is modified to reflect the change.




Well, you might be naive (talented?) enough to write your own framework to manage the slowly changing dimensions in your environment, however it is good to know that there is a handy tool at your disposal to minimize the plumbing works for those data management scenarios.

As of IBM DB2 version 8.1 FixPack 2 or later, MERGE statement is introduced.

The example below illustrates how Merge can take data from a staging table and use it to update or create dimension members accordingly.


MERGE INTO
DWH.DIM_COMPANY AS TARGET
USING
(SELECT ID, NAME, PHONE FROM DWHSTAG.COMPANY) AS SOURCE
ON SOURCE.ID = TARGET.ID
WHEN MATCHED THEN
UPDATE SET (NAME,PHONE)=(SOURCE.NAME,SOURCE.PHONE)
WHEN NOT MATCHED THEN
INSERT (ID, NAME, PHONE) VALUES (SOURCE.ID, SOURCE.NAME, SOURCE.PHONE)
ELSE IGNORE;


Do you think life is better now?

Complete Syntax on DB2 Merge Statement

Wednesday, December 05, 2007

DB2 Changing Statement Terminator Symbol

When you pass in -t command option to DB2 CLP, the delimiter ; (Semi colon) is turn on for enabling you to enter multiple statement lines before submitting it. This is typically acceptable behavour when you need to submit a single statement that encompassing multiple lines in the console.

What if you need to submit a large and complex stored procedure creation script to CLP? You can save the codes in a physical file then use db2 -t -f myProc.sql.

Then you hit errors that doesn't make sense at all.

Most probably the query parser is confused with the statement terminator and your substatement terminator. Substatements, such as those that you embedded in your stored procedure are forced to use ; as their termination character. In this case, when the parser encounter the first embedded semicolon, it would think that the statement is ready for parsing. Ta da. it strew up.

So, a better command would be "db2 -td# -f myProc.sql" assuming you are using # symbol as your statement terminator.

Yet, you might face another issue of encountering multiple different statement terminator in the same CLP session.

So you decide to

db2 -td#
select * from syscat.tables#
quit#

db2 -td$
select * from syscat.columns$
quit$

This example is trivial, but you get my point.

So, is there a better solution? O yeah, you can use one of the DB2 Control Option in the form of:

--#SET TERMINATOR

For example:

db2 -t
SELECT * FROM SYSCAT.TABLES FETCH FIRST 1 ROW ONLY;
--#SET TERMINATOR #
SELECT * FROM SYSCAT.COLUMNS FETCH FIRST 1 ROW ONLY#
--#SET TERMINATOR $
VALUES (1)$

Similar approach can be adopted in Java DB2 programming by submitting it as part of the query you sent to DB2.

Wednesday, April 25, 2007

DB2 Restore DB Command

Personally, I prefer to use Restore DB DB2 Command, instead of that Restore Database Wizard in Control Center. Don't know why, maybe I easily get confused by buttons and drop downs. ;-)

Here I will run down a simulation of backing up and restoring the database under different name in separate DB2 instance resides within same physical machine. Similar scenario might be to replicate databases for development, testing and production.

First, Fire up the Command Window (db2cmd)

Create the Development instance:

db2icrt DEV


Start the DEV instance


SET DB2INSTANCE=DEV
db2start


Create the sample database


CREATE DB DEVDB


Check out the list of tablespaces in the database, this is important when later you need to redirect the creation of tablespace containers.


db2 connect to DEVDB
db2 list tablespaces



Backup the sample DEVDB database. This will create a folder DEVDB.0 under C:


db2 BACKUP DB DEVDB TO C:


Create the Production instance:

db2icrt PROD


Start the PROD instance


SET DB2INSTANCE=PROD
db2start


Restore the backup DEVDB under C: to PRODDB in PROD instance.



db2 restore db DEVDB FROM C: INTO PRODDB redirect
db2 set tablespace containers for 0 using (path 'C:\container\tspace00c1')
db2 set tablespace containers for 1 using (path 'C:\container\tspace01c1')
db2 set tablespace containers for 2 using (path 'C:\container\tspace02c1')
db2 restore db DEVDB continue



Try to connect to the new PRODDB restored from DEVDB


db2 connect to PRODDB



The trick here is to specify a separate set of container paths for Restore DB command. This will depends on number of tablespaces used by your DB. Default is only 3 tablespaces: SYS, TEMP and USER.


To clean up the simulation, do the following:


set DB2INSTANCE=DEV
db2stop force
set DB2INSTANCE=PROD
db2stop force

db2idrop DEV
db2idrop PROD

Manually remove C:\DEV, C:\PROD, C:\DEVDB.0 and C:\container

Thursday, October 26, 2006

Enable Commenting (Comments)

An excerpt from Alphablox 8.4 documentation:


CommentsBlox allows you to provide cell commenting (also known as cell annotations) functionality to your application. In addition, you can use CommentsBlox for general commenting that are not tied to any other Blox. For example, you can allow users to add comments to a site, an application, a report, or a Web page.

Comments are stored in a JDBC accessible relational database. Supported databases include IBM(R) DB2(R) UDB, Sybase, Microsoft(R) SQL Server, and Oracle. This data source needs to be defined to DB2 Alphablox. DB2 Alphablox provides a Comments Management page under the Server link in the DB2 Alphablox Administration tab that lets you specify the relational data source to use for storing comments. From that page, you can create "collections" (data tables) to store comments. For cell-level comments, you will need to specify the multidimensional data source used in your GridBlox, the cube to use (for Microsoft Analysis Services), and the dimensions to include. For general comments, you only need to specify the name.


Here is what you need to do to get a feel of the built-in CommentsBlox in Alphablox.

First, go to Alphablox Administration Site, by default should be accessible from http://yourserver:9080/AlphabloxAdmin/

From the Administration main tab, you should be in General paage by default. There is a Runtime Management section, amongst others. Click on the Comments link under this section. A pop-up window should appear that refer to http://yourserver:9080/AlphabloxAdmin/comments/commentsAdmin.jsp.

Here you gotta define the comments collection (sort of like a repository to store a particular set of comments) and maybe customize the set of fields used for commenting.

Choose a data source that you already defined elsewhere, enter the security credential and click Connect button. All existing collections should appear in the list provided.


Click Create button, since you want to create a new collection. You need to fill in the details for the form provided, particularly you need to specify the dimension to enable the comments and the fields required for the comment entry.



Click Save once you done.

Ok, you have done with the necessary configuration.

In your Blox programming, you can do the following:

1. Enabling Commenting for GridBlox
...
<blox:data><blox:comments collectionName="YourCommentCollectionName" dataSourceName="TheDataSourceUsedForCollection" /></blox:data>
...

...
<blox:grid commentsEnabled="true" />
...

Then when you right click on your grid cell, an option called "Comments" will appear.


2. Use com.alphablox.blox.CommentsBlox and related com.alphablox.blox.comments.*

This allows you to implement Commenting feature for your relational reports or any other general usage. Requires programming though.

Saturday, October 14, 2006

DB2 How to Empty a Table

In data warehouse environment, usually there is a need to clear out the contents of staging tables to prepare for a fresh set of extracted source data. You might also want to housekeep some historical aggregation according to some predefined schedules, which involves copying data from a table to another and subsequently removed all data from the copied table. These are just some of the examples that boiled down to the need of efficiently "truncate" a table.

Microsoft SQL Server and Oracle DBA are definitely enjoying the luxury of built-in table truncation functionality, through command like "TRUNCATE TABLE YourTableName".
Ok fine, but does IBM DB2 UDB, the so-called most scalable and performing RDBMS provide such option?

Before that, let me evaluate some of the options of removing rows from a table.

Option A:

DELETE FROM YourTable

Well, you can delete all rows using this statement. However when involving lot of records, transaction logging causes significant performanc degradation. Still, this option is acceptable if your application requires recovery of deleted rows.


Option B:


(Assume this is within the same transaction)
ALTER TABLE YourTable ACTIVATE NOT LOGGED INITIALLY;
DELETE FROM YourTable;


You managed to escape the bad luck of doing a lot of transaction logging. But wait a minute, constraint checking are still in force (Check yourself by doing explaining a DELETE FROM statement).


Option C:


(Assume this is within the same transaction)
SET INTEGRITY FOR YourTable OFF;
ALTER TABLE YourTable ACTIVATE NOT LOGGED INITIALLY;
DELETE FROM YourTable;


Again, you managed to skip the logging and check constraint and referential constraint checking, datalink integrity checking, and generation of values for generated columns. Primary/Unique Key constraints still enforced.

Option D:


LOAD FROM /dev/null of del REPLACE INTO YourTable


This is by far the most common workaround that I have seen for table truncation. It basically uses the LOAD utility on /dev/null for simulating the loading (replace) of no-data into the designated table. The same concept works for Windows environment.

You can also use similar IMPORT FROM /dev/null of DEL REPLACE INTO YourTable. There are some differences between IMPORT/LOAD.

Option E:

With a little bit of guts, you can drop and recreate the tables. This can be tedious if you got to recreate every constraints/views/etc that dependent on the "new" table.

Option F:


ALTER TABLE YourTable ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE


This is by far my favorite way of doing "TRUNCATE Table" in DB2.

Which ring suits you? You decide.

Sunday, September 10, 2006

Mistake Mistake

The multithreaded Java application I wrote in April 06 for extracting data from 90k files to IBM DB2 v8.2 system suffering from performance bottleneck when dealing with too many worker threads. This is a common issue for multithreading programming and that's why we got something called Thread Pool. While waiting for the extraction to be finished (God, it has been running for 30 hours and still executing at this moment), I took some initiatives to perform a code review, trying to identify possible programming faults. And oh yeah, found a potential program problem. See figures below




What you think is wrong with the above code snippets? Well, I try to recall the rationale of doing the stuff like that when I was developing this program and I think I did that to avoid the intricacy of thread synchronization due to very constrained assigned time frame.

I wouldn't tell you what's wrong with it and I hope you can spend some time exploring it out. However I can tell you that one of the way to get out from this scalability issue is to using java.lang.ThreadLocal to manage the connection instead.

Tuesday, March 14, 2006

Be Smart. Get A Row Number !

Imagine there is a table with no primary key or unique constraint and volumes of duplicated rows keep coming in. The only piece of information that differentiates these rows is a timstamp column that stores the date and time where record is inserted.








ID (VARCHAR)NAME (VARCHAR)AMOUNT (INT)LAST_UPDATED (TIMESTAMP)
1Eddy80000.002006-03-14-00.10.31.999999
1Eddy90000.002006-03-14-00.09.31.999999
1Eddy90000.002006-03-14-00.11.31.999999
2Lee Sin Ti100.002006-03-14-00.10.31.999999
2Lee Sin Ti200.002006-03-14-00.09.31.999999

Table1


Based on this, your DB2 query is to remove all the outdated records while maintaining only the latest entries.


An idiotic first attempt:

DELETE FROM TABLE1
WHERE
ID || NAME || CAST (AMOUNT AS VARCHAR(32)) || CAST(LAST_UPDATED AS VARCHAR(64))
NOT IN
(
SELECT (ID || NAME || CAST(AMOUNT AS VARCHAR(32)) || CAST(MAX(LAST_UPDATED) AS VARCHAR(64)) ) AS KEY FROM TABLE1 GROUP BY ID, NAME
);

This attempt is definitely a NO-NO. Not only the string concatenations takes a huge amount of processing cycles, it is also UGLY in my point of view. Using a generated random data of 100k records, it takes an unacceptable amount of time to complete the delete query.

Then, my second attempt got to deal with DB2 support of row_number() function, which I greatly appreciated from IBM.


Second attempt:

DELETE FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY ID, NAME ORDER BY LAST_UPDATED DESC)
FROM TABLE1
) AS X (ROWNUM) WHERE ROWNUM > 1;

WOW, an optimized yet elegant query to achieve my goal. With the same random data set, it only took less than 1 minute to complete the query.