Specifying null value attributes through column properties?

[Ultramundane]

I have searched this forum on the many ways to handle null values. I saw several posts that specified that some handling can be setup on the source and target stages through the "edit column metadata properites" dialog box. However, when I try to use this method, (Note: I have tried for all of the database stages) I cannot specify anything. I have tried for char nullable, varchar nullable, decimal nullable, date nullable, etc...

I always get
Properties:
None Available

No schema properties can be specified at this point.

I must be missing something? I have tried the documentation and it is not helping me with what I am missing.

[vmcburney]

You don't need to do anything special for database input stages, they accurately read the metadata of the table via table import and the nullubility settings are taken from the table definition. This ensures all data can be read from the table including nulls. You then need to handle nulls in a modify or transform stage if you are delivering the data to reporting users or targets that do not allow nulls.

Null handling can be added to a sequential file input stage as text files do not hold null values, instead you have to tell the stage how to handle empty strings.

[Ultramundane]

That is what I thought, but my Datastage jobs blow-up when timestamp, char, and integer columns are configured as NULLABLE and I do not specify an alternate isnull value. I am just testing at the moment and both tables have the same schema. So, just a straight download and upload of records. However, when Datastage encounters the NULL datastage blows-up. I can copy the same records invoking the command line utilites bcp (Sybase) or mload (Teradata) without any problems.

Sounds like a bug then?

[ray.wurlod]

No. Sounds like inconsistent handling of nullability. The problem is between the keyboard and the chair.

[vmcburney]

Your DataStage job will have a problem if you try to run any type of derivation on a field that passes a null value, such as a comparison or calculation. The fields that are passed straight through without transformation should be okay. You will have to post the error message for more help.

[Ultramundane]

I believe it is a bug. I don't think my chair or keyboard has anything to do with the issue. Maybe my brain or this product has the issue. But not my keyboard or chair as they are inanimate objects.

Anyways, the job is simple, straight database to database.

DB -> DB. I created the tables with the exact same DDL except table name.

Now, t1 and t2 have some null columns (same columns and names) and datastage will not load the columns that are null.

Thanks vmcburney for your response. I am not doing any transformations or derivations on the columns. Just t1.all columns -> t2.all columns straight. If I use the ODBC stage and not Ascential's native Database stage, the job works fine. Also, The native stage will not blow-up if I change the NULLs in colb to an actual value. Thus, an Ascential bug in the implementation of this native database stage. I know it is not a problem with BCP as I can copy the data through the utility without any problems.

Here are DS error messages:
DataStage Report - Detail Log for job: Syb_BCP_with_NULL_TS_SEnt
Produced on: 1/4/2006 9:26:41 AM
Project: FMds01
Host system: rcrpdev1.blg982.meijer.com
Items: 1 - 24
Sorted on: Date Sorter

Item #: 1
Event ID: 0
Timestamp: 2006-01-04 09:25:57
Type: Control
User Name: dsadm
Message: Starting Job Syb_BCP_with_NULL_TS_SEnt.

Item #: 2
Event ID: 1
Timestamp: 2006-01-04 09:25:58
Type: Info
User Name: dsadm
Message: Environment variable settings:
_=/usr/bin/nohup
BFNC=/Meijer_work/Ascential/func
LANG=en_US
BCFG=/Meijer_work/Ascential/config
LOGIN=dsadm
BLOG=/Meijer_work/Ascential/log
VISUAL=/usr/bin/vi
BTMP=/Meijer_work/Ascential/tmp
APT_ORCHHOME=/Meijer_sfwe/Ascential/Ascential/DataStage/PXEngine
PATH=/Meijer_sfwe/Ascential/Ascential/DataStage/Projects/FMds01/wrapped:/Meijer_sfwe/Ascential/Ascential/DataStage/Projects/FMds01/buildop:/Meijer_sfwe/Ascential/Ascential/DataStage/Projects/FMds01/RT_BP148.O:/Meijer_sfwe/Ascential/Ascential/DataStage/DSCAPIOp:/Meijer_sfwe/Ascential/Ascential/DataStage/RTIOperators:/Meijer_sfwe/Ascential/Ascential/DataStage/DSParallel:/Meijer_sfwe/Ascential/Ascential/DataStage/PXEngine/user_osh_wrappers:/Meijer_sfwe/Ascential/Ascential/DataStage/PXEngine/osh_wrappers:.:/usr/bin:/etc:/usr/sbin:/usr/ucb:/home/dsadm/bin:/usr/bin/X11:/sbin:/Meijer_sfwe/Ascential/Ascential/DataStage/DSEngine/bin/:/Meijer_sfwe/Ascential/Ascential/DataStage/processmb:/msybase/OCS-12_5/bin:/home/db2inst8/sqllib/bin:/home/db2inst8/sqllib/adm:/u00/app/oracle/product/9.2/bin:/usr/bin:/usr/mqm/bin:/Meijer_sfwe/Ascential/Ascential/DataStage/PXEngine/bin:/Meijer_work/Ascential/script:/Meijer_sfwe/Ascential/Ascential/DataStage/processmb:/msybase/OCS-12_5/bin:/home/db2inst8/sqllib/bin:/home/db2inst8/sqllib/adm:/u00/app/oracle/product/9.2/bin:/usr/bin:/usr/mqm/bin:/Meijer_sfwe/Ascential/Ascential/DataStage/PXEngine/bin:/usr:/usr/vacpp:/usr/vacpp/bin
DB2DIR=/usr/opt/db2_08_01
COPERR=/usr/lib
LC__FASTMSG=true
CGI_DIRECTORY=/var/docsearch/cgi-bin
BDIR=/Meijer_work/Ascential
LOGNAME=dsadm
COPLIB=/usr/lib
MAIL=/usr/spool/mail/dsadm
LOCPATH=/usr/lib/nls/loc
PS1=dsadm@rcrpdev1:$PWD)
SYBASE=/msybase
BPGM=/Meijer_work/Ascential/script
DOCUMENT_SERVER_MACHINE_NAME=localhost
USER=dsadm
AUTHSTATE=compat

DCE_USE_WCHAR_NAMES=1
DEFAULT_BROWSER=netscape
TD_ICU_DATA=/opt/tdicu/lib
SHELL=/usr/bin/ksh
DSHOME=/Meijer_sfwe/Ascential/Ascential/DataStage/DSEngine
BENV=/Meijer_work/Ascential/env
ODMDIR=/etc/objrepos
DOCUMENT_SERVER_PORT=49213

PAM_SERVICE=su
ODBCINI=/Meijer_sfwe/Ascential/Ascential/DataStage/DSEngine/.odbc.ini
HOME=/home/dsadm
DB2INSTANCE=db2inst8
TERM=
MAILMSG=[YOU HAVE NEW MAIL]
ORACLE_HOME=/u00/app/oracle/product/9.2
PWD=/Meijer_sfwe/Ascential/Ascential/DataStage/DSEngine
DOCUMENT_DIRECTORY=/usr/docsearch/html
TZ=EST5EDT
SYBASE_OCS=OCS-12_5
INSTHOME=/home/db2inst8
BRCV=/Meijer_work/Ascential/recovery
A__z=! LOGNAME
UDTHOME=/Meijer_sfwe/Ascential/Ascential/DataStage/ud41
UDTBIN=/Meijer_sfwe/Ascential/Ascential/DataStage/ud41/bin
DS_USERNO=-17120
WHO=FMds01
BELL=^G
FLAVOR=-1
LIBPATH=/Meijer_sfwe/Ascential/Ascential/DataStage/Projects/FMds01/RT_BP148.O:/Meijer_sfwe/Ascential/Ascential/DataStage/DSCAPIOp:/Meijer_sfwe/Ascential/Ascential/DataStage/RTIOperators:/Meijer_sfwe/Ascential/Ascential/DataStage/DSParallel:/Meijer_sfwe/Ascential/Ascential/DataStage/PXEngine/user_lib:/Meijer_sfwe/Ascential/Ascential/DataStage/Projects/FMds01/buildop:/Meijer_sfwe/Ascential/Ascential/DataStage/PXEngine/lib://Meijer_sfwe/Ascential/Ascential/DataStage/branded_odbc/lib:/Meijer_sfwe/Ascential/Ascential/DataStage/DSEngine/lib:/Meijer_sfwe/Ascential/Ascential/DataStage/DSEngine/uvdlls:/usr/opt/db2_08_01/lib:/home/db2inst8/sqllib/lib:/u00/app/oracle/product/9.2/lib32:/usr/lib:/usr/mqm/lib:/Meijer_sfwe/Ascential/Ascential/DataStage/DSEngine/java/jre/bin/classic:/Meijer_sfwe/Ascential/Ascential/DataStage/DSEngine/java/jre/bin:/msybase/OCS-12_5/lib:/lib
TMPDIR=/Meijer_work/Ascential/tmp
DSIPC_OPEN_TIMEOUT=30
APT_CONFIG_FILE=/Meijer_sfwe/Ascential/Ascential/DataStage/Configurations/default.apt
APT_MONITOR_MINTIME=10
DS_OPERATOR_BUILDOP_DIR=buildop
DS_OPERATOR_WRAPPED_DIR=wrapped
DS_ENABLE_RESERVED_CHAR_CONVERT=0
DS_TDM_PIPE_OPEN_TIMEOUT=720
DS_TDM_TRACE_SUBROUTINE_CALLS=0
APT_COMPILEOPT=-O -c -qspill=32704
APT_COMPILER=/usr/vacpp/bin/xlC_r
APT_LINKER=/usr/vacpp/bin/xlC_r
APT_LINKOPT=-G
APT_TERA_64K_BUFFERSIZE=2048
OSH_STDOUT_MSG=1
APT_ERROR_CONFIGURATION=severity, !vseverity, !jobid, moduleid, errorIndex, timestamp, !ipaddr, !nodeplayer, !nodename, opid, message
APT_OPERATOR_REGISTRY_PATH=/Meijer_sfwe/Ascential/Ascential/DataStage/Projects/FMds01/buildop


Item #: 3
Event ID: 2
Timestamp: 2006-01-04 09:25:58
Type: Info
User Name: dsadm
Message: Parallel job initiated
# OSH / orchestrate script for Job Syb_BCP_with_NULL_TS_SEnt compiled at 09:25:52 04 JAN 2006
#################################################################
#### STAGE: Sybase_Enterprise_0
## Operator
asesybasereade
## Operator options
-server 'SL0982LP'
-user 'sa'
-password [&__V0S0P1_password]
-db_name 'tempdb'
-table 'ryan_tb1'

## General options
[ident('Sybase_Enterprise_0'); jobmon_ident('Sybase_Enterprise_0')]
## Outputs
0> [modify (
keep
cola,colb,colc;
)] 'Sybase_Enterprise_0:DSLink2.v'
;

#################################################################
#### STAGE: Sybase_Enterprise_3
## Operator
asesybasewrite
## Operator options
-server 'SL0982LP'
-user 'sa'
-password [&__V0S3P1_password]
-table 'ryan_tb2'
-mode truncate
-db_name 'tempdb'

## General options
[ident('Sybase_Enterprise_3'); jobmon_ident('Sybase_Enterprise_3')]
## Inputs
0< 'Sybase_Enterprise_0:DSLink2.v'
;


# End of OSH code

Item #: 4
Event ID: 3
Timestamp: 2006-01-04 09:26:01
Type: Info
User Name: dsadm
Message: main_program: Ascential DataStage(tm) Enterprise Edition 7.5.1A
Copyright (c) 2004, 1997-2004 Ascential Software Corporation.
All Rights Reserved

Item #: 5
Event ID: 4
Timestamp: 2006-01-04 09:26:02
Type: Info
User Name: dsadm
Message: main_program: orchgeneral: loaded
orchsort: loaded
orchstats: loaded

Item #: 6
Event ID: 5
Timestamp: 2006-01-04 09:26:02
Type: Info
User Name: dsadm
Message: main_program: APT configuration file: /Meijer_sfwe/Ascential/Ascential/DataStage/Configurations/default.apt
{
node "rcrpdev101"
{
fastname "rcrpdev1"
pools ""
resource disk "/Meijer_work/Ascential/Dset01" {pools ""}
resource scratchdisk "/Meijer_work/Ascential/Scr01" {pools ""}
}
node "rcrpdev102"
{
fastname "rcrpdev1"
pools ""
resource disk "/Meijer_work/Ascential/Dset01" {pools ""}
resource scratchdisk "/Meijer_work/Ascential/Scr01" {pools ""}
}
}

Item #: 7
Event ID: 6
Timestamp: 2006-01-04 09:26:03
Type: Info
User Name: dsadm
Message: main_program: SCW_INFO: Initiating SCW
SRVER MSG: Changed database context to 'master'.
SCW_INFO:Connection Established
SRVER MSG: Changed database context to 'tempdb'.
SCW_INFO: Initiating SCW
SRVER MSG: Changed database context to 'master'.
SCW_INFO:Connection Established
SRVER MSG: Changed database context to 'tempdb'.

Item #: 8
Event ID: 7
Timestamp: 2006-01-04 09:26:03
Type: Info
User Name: dsadm
Message: Sybase_Enterprise_0: Ret Success __describeOperator__

Item #: 9
Event ID: 8
Timestamp: 2006-01-04 09:26:03
Type: Info
User Name: dsadm
Message: main_program: SCW_INFO: Initiating SCW
SRVER MSG: Changed database context to 'master'.
SCW_INFO:Connection Established
SRVER MSG: Changed database context to 'tempdb'.
SCW_INFO: Initiating SCW
SRVER MSG: Changed database context to 'master'.
SCW_INFO:Connection Established
SRVER MSG: Changed database context to 'tempdb'.

Item #: 10
Event ID: 9
Timestamp: 2006-01-04 09:26:04
Type: Info
User Name: dsadm
Message: Sybase_Enterprise_3: Ret Sucess __describeOperator__

Item #: 11
Event ID: 10
Timestamp: 2006-01-04 09:26:04
Type: Info
User Name: dsadm
Message: Sybase_Enterprise_0,0: SCW_INFO: Initiating SCW
SRVER MSG: Changed database context to 'master'.
SCW_INFO:Connection Established
SRVER MSG: Changed database context to 'tempdb'.

Item #: 12
Event ID: 11
Timestamp: 2006-01-04 09:26:04
Type: Warning
User Name: dsadm
Message: Sybase_Enterprise_3,0: At field "colb": "null_field" length (0) must match field's fixed width (24)

Item #: 13
Event ID: 12
Timestamp: 2006-01-04 09:26:04
Type: Info
User Name: dsadm
Message: Sybase_Enterprise_3,0: Export Complete

Item #: 14
Event ID: 13
Timestamp: 2006-01-04 09:26:04
Type: Warning
User Name: dsadm
Message: Sybase_Enterprise_3,0: At field "colb": Exporting a fixed length field with a "null_field" of length 0 will prohibit a re-import of this field

Item #: 15
Event ID: 14
Timestamp: 2006-01-04 09:26:04
Type: Info
User Name: dsadm
Message: Sybase_Enterprise_3,0: subproc o/p message:
subproc o/p message: Starting copy...
subproc o/p message: CSLIB Message: - L0/O0/S0/N24/1/0:
subproc o/p message: bcp copy in partially failed
subproc o/p message:
subproc o/p message: 1 rows copied.
subproc o/p message: Clock Time (ms.): total = 38 Avg = 38 (26.32 rows per sec.)

Item #: 16
Event ID: 15
Timestamp: 2006-01-04 09:26:04
Type: Fatal
User Name: dsadm
Message: Sybase_Enterprise_3,0: The runLocally() of the operator failed.

Item #: 17
Event ID: 16
Timestamp: 2006-01-04 09:26:07
Type: Info
User Name: dsadm
Message: Sybase_Enterprise_3,0: Input 0 consumed 0 records.

Item #: 18
Event ID: 17
Timestamp: 2006-01-04 09:26:07
Type: Fatal
User Name: dsadm
Message: Sybase_Enterprise_3,0: processing failed for output connection 1: exit status = 255.Sybase_Enterprise_3

Item #: 19
Event ID: 18
Timestamp: 2006-01-04 09:26:07
Type: Fatal
User Name: dsadm
Message: Sybase_Enterprise_3,0: The runLocally() of the operator failed.

Item #: 20
Event ID: 19
Timestamp: 2006-01-04 09:26:07
Type: Info
User Name: dsadm
Message: Sybase_Enterprise_3,0: Input 0 consumed 0 records.

Item #: 21
Event ID: 20
Timestamp: 2006-01-04 09:26:13
Type: Fatal
User Name: dsadm
Message: Sybase_Enterprise_3,0: Operator terminated abnormally: runLocally did not return APT_StatusOk

Item #: 22
Event ID: 21
Timestamp: 2006-01-04 09:26:13
Type: Fatal
User Name: dsadm
Message: main_program: Step execution finished with status = FAILED.

Item #: 23
Event ID: 22
Timestamp: 2006-01-04 09:26:13
Type: Info
User Name: dsadm
Message: main_program: Startup time, 0:07; production run time, 0:04.

Item #: 24
Event ID: 23
Timestamp: 2006-01-04 09:26:13
Type: Control
User Name: dsadm
Message: Job Syb_BCP_with_NULL_TS_SEnt aborted.

End of report.