NULL Handling in Sequential File Stage

DataStage has a mechanism for denoting NULL field values. It is slightly different in server and parallel jobs. In the sequential file stage a character or string may be used to represent NULL column values. Here's how represent NULL with the character "~":

Server Job:
1. Create a Sequential file stage and make sure there is an Output link from it.
2. Open the Sequential file stage and click the "Outputs" tab ans Select "Format"
3. On the right enter the "~" next to "Default NULL string:"

Parallel Job:
1. Create a Sequential file stage and make sure there is an Output link from it.
2. Open the Sequential file stage and click the "Outputs" tab ans Select "Format"
3. Right click on "Field defaults" ==> "Add sub-property" and select "Null field value"
4. Enter the "~" in the newly created field.

Like the below page to get update  
https://www.facebook.com/datastage4you
https://twitter.com/datagenx
https://plus.google.com/+AtulSingh0/posts
https://datagenx.slack.com/messages/datascience/

Read More

vmware player powering on internal error

Last night, I struggled with "vmware player powering on internal error" for almost an hour when I was trying to run a vmware os on my machine and followed so many tweaks to resolve this but no success.
After lots of googling, I found one solution which worked for me So sharing the same here if it can help someone stuck like me :)

Read More

Jenkins with Windows #3

Continuing my last posts (Jenkins with Windows #1 & Jenkins with Windows #2)..I am adding some more tips or solution with Jenkins installation.

Install Jenkins as a Windows service - 
You can follow the Jenkins Wiki page to create a windows service for jenkins so whenever you have to start/stop/restart, you no need to open a command prompt and type some commands.
It will install as a service in windows which you can see in services (services.msc).  Please find the page link as below -

Jenkins Wiki Page Link - Install Jenkins as a Windows service

Configuring Jenkins as a Windows service -
When you install jenkins as windows service, you have to change/check jenkins.xml file created in JENKINS_HOME directory.

If you are using port other than 8080, Do the below changes in jenkins.xml Else leave as it is.

<!--
    if you'd like to run Jenkins with a specific version of Java, specify a full path to java.exe.
    The following value assumes that you have java in your PATH.
  -->
  <executable>java</executable>
  <arguments>-Xrs -Xmx256m -Dhudson.lifecycle=hudson.lifecycle.WindowsServiceLifecycle -jar "%BASE%\jenkins.war" --httpPort=9090 --webroot="%BASE%\war"</arguments>

Change the httpPort to port where you want to run your jenkins and then restart the jenkins service. Now you can access jenkins on http://localhost:9090

Like the below page to get update  
https://www.facebook.com/datastage4you
https://twitter.com/datagenx
https://plus.google.com/+AtulSingh0/posts
https://datagenx.slack.com/messages/datascience/

Read More

Jenkins with Windows #2

In last post, we covered What is jenkins and it's benefits (http://bit.ly/2e7mNbb). Today we are going to cover it's installtion on windos.

Prerequisite & Installation Steps:

1. Your system should have latest java installed and java path should be configured. You can check it as below - 

  
2.  Download the letest win-bash which is required by Jenkin - https://sourceforge.net/projects/win-bash/


3. Extract the win-bash zip file and add the folder path to system environment variable PATH. You can check whether it is configured it or not by executing linux command

4. Check if your 8080 port is available
              netstat -ano | findstr 8080
 If you get any output like below, It means 8080 port is not available. 

5. Configure the JENKINS_HOME variable (add JENKINS_HOME in environment variable and add JENKINS_HOME into PATH variable too.

 6. Add "C:\Windows\SysWOW64" in your PATH
 

7. Go to JenKins home page - https://jenkins.io
8. Download the latest jenkins.war files by clicking on Download Jenkins

9. Execute the below command to install the jenkins

If your 8080 port is available then -
               java -jar jenkins.war
else
               java -jar jenkins.war --httpPort=9090 (you can give any available port)

10. When installation is complete, (Do not close your installation command prompt) Open below link in web browser -
               http://localhost:9090/
       This will route you too, very first screen of jenkins as below -

11. The password you can get in either installation log as below Or the directory mentioned in the page - 

12. After successful login, you will be moved to plugin screen (Choose Install suggested plugins) -

 Plugin installation -

12. After Plugin installation, create a new admin user - 

 

13. After user creation, Choose Save and Close

14. Refresh the page and login with new admin credentials you created.

 

15. Jenkins Installation is completed.

 In next post, we will see How to install Jenkins as windows service and What if you missed the new admin user creation.

Like the below page to get update  
https://www.facebook.com/datastage4you
https://twitter.com/datagenx
https://plus.google.com/+AtulSingh0/posts
https://datagenx.slack.com/messages/datascience/

Read More

#2 DataStage Solutions to Common Warnings/Error - Null Handling

Warnings/Errors Related to Null Handling -

1.1       When checking operator: When binding output interface field “XXXXX” to field “XXXXX”: Converting a nullable source to a non-nullable result

Cause: This can happen when reading from oracle database or in any processing stage where input column is defined as nullable and metadata in datastage is defined as non-nullable.

Resolution: Convert a nullable field to non  nullable. Need to apply available null functions in datastage or in the query.


1.2       APT_CombinedOperatorController(1),0: Field 'XXXXX' from input dataset '0' is NULL. Record dropped.

Cause: This can happen when there is no null handling mentioned on column and the same column is used in constraints/Stage Varibales.

Resolution:  Provide Null handling function to the column mentioned in constraint/Stage variable.

1.3       Fatal Error: Attempt to setIsNull() on the accessor interfacing to non-nullable field "XXXX".

Cause: This can happen when the column in source is nullable but in DB2 stage its mentioned as Non Nullable

Resolution: Change the Nullable field for the column to “Yes” instead of “No” i.e.


1.4       Exporting nullable field without null handling properties

Cause: This can happen when the columns are mentioned as nullable in sequential file stage and no representation for null values was specified.

Resolution: Specify Null field value in Format tab of sequential file stage.

Like the below page to get update  
https://www.facebook.com/datastage4you
https://twitter.com/datagenx
https://plus.google.com/+AtulSingh0/posts
https://datagenx.slack.com/messages/datascience/

Read More

NULL behaviour in Oracle

NULL, as known in SQL, a special marker and keyword indicating that something has no value. A value of NULL is different from an empty or zero value. No two null values are equal. Comparisons between two null values, or between a NULL and any other value, return unknown because the value of each NULL is unknown. (ref - https://technet.microsoft.com/en-us/library/ms191504(v=sql.105).aspx)

For understanding it, I am using a practical approach where we play around NULL value, sorry No Value, with Oracle database.

** NULL value doesn't count in aggregate functions.
NULL value doesn't count by aggregate functions such as MAX, MIN, SUM, COUNT in SQL.

Example:


** Inserted null string converted to Null.

Example:


** Where can Null be compared?
Example:


Oracle SQL Reference says "In a DECODE function, Oracle considers two nulls to be equivalent. If expr is null, then Oracle returns the result of the first search that is also null."
But this therory varies with DB to DB internal architecture and the way they handled the NULL.Another place where Null can be compared is in range partition definition, where MAXVALUE is greater than Null

**  Unique constraints
Example:


You are able to insert another Null without getting ORA-1 (unique constraint violated).
--
Example:


So if all columns are null, the unique constraint will not be violated. If one or more columns have non-null values, the constraint takes effect.


** Unknown OR True returns True, Unknown AND False returns False
Example:



 
Like the below page to get update  
https://www.facebook.com/datastage4you
https://twitter.com/datagenx
https://plus.google.com/+AtulSingh0/posts
https://datagenx.slack.com/messages/datascience/

Read More