#428 Decimal Values will be returned formated

[Claus]

OK:

select cast ( 123456.78 as decimal (8,2)) from sysibm.sysdummy1;
Result: 123456.78

Not OK:

select cast ( 123456.0 as decimal (8,2)) from sysibm.sysdummy1;
Result: 123456.00

It should be

123456

How can I change this behaviour? Why do I get a ".00"?

[John Eberhard]

The data type is Decimal(8,2) so it will always display the 2 digits after the decimal point.

[Claus]

Sorry, but this cannot be true by default:

We have two different AS400 / System i installations.

Here it works:

JDBC-Treiber Klassenname    com.ibm.as400.access.AS400JDBCDriver
JDBC-Treiber-Klassenpfad    D:\Tools\JDBC\DB2-jtOpen\java8\jt400.jar
getURL  jdbc:as400://yyy
getDriverName   AS/400 Toolbox for Java JDBC Driver
getDatabaseProductName  DB2 UDB for AS/400
getDatabaseProductVersion   07.03.0000 V7R3m0
getDriverVersion    12.1
getUserName HVRF1060
getDefaultTransactionIsolation  TRANSACTION_READ_UNCOMMITTED
isReadOnly  false
supportsSchemasInTableDefinitions   true
supportsCatalogsInTableDefinitions  false
supportsSchemasInDataManipulation   true
supportsCatalogsInDataManipulation  false
getSchemaTerm   Library
getProcedureTerm    Procedure
getCatalogTerm  Database
getDatabaseMajorVersion 7
getIdentifierQuoteString    "
supportsCatalogsInProcedureCalls    false
getJDBCMinorVersion 2
supportsStoredProcedures    true
getJDBCMajorVersion 4
supportsSavepoints  true
getCatalogSeparator .
storesUpperCaseIdentifiers  true
supportsMultipleResultSets  true
getResultSetHoldability 1
allProceduresAreCallable    false
allTablesAreSelectable  false
autoCommitFailureClosesAllResultSets    false
dataDefinitionCausesTransactionCommit   false
dataDefinitionIgnoredInTransactions false
doesMaxRowSizeIncludeBlobs  true
generatedKeyAlwaysReturned  false
getClientInfoProperties ApplicationName 255     The name of the application currently utilizing the connection.
ClientUser  255     The name of the user that the application using the connection is performing work for.  This may not be the same as the user name that was used in establishing the connection.
ClientHostname  255     The hostname of the system the application using the connection is running on.
ClientAccounting    255     Accounting information.
ClientProgramID 255     Program identification information.

getDatabaseMinorVersion 3
getDriverMajorVersion   12
getDriverMinorVersion   1
getExtraNameCharacters  $@#
getMaxBinaryLiteralLength   32739
getMaxCatalogNameLength 10
getMaxCharLiteralLength 32739
getMaxColumnNameLength  128
getMaxColumnsInGroupBy  8000
getMaxColumnsInIndex    120
getMaxColumnsInOrderBy  10000
getMaxColumnsInSelect   8000
getMaxColumnsInTable    8000
getMaxConnections   0
getMaxCursorNameLength  128
getMaxIndexLength   2000
getMaxLogicalLobSize    2147483647
getMaxProcedureNameLength   128
getMaxRowSize   32766
getMaxSchemaNameLength  128
getMaxStatementLength   1048576
getMaxStatements    9999
getMaxTableNameLength   128
getMaxTablesInSelect    1000
getMaxUserNameLength    10
getRowIdLifetime    ROWID_VALID_FOREVER
getSQLStateType 2
getSearchStringEscape   \
isCatalogAtStart    true
locatorsUpdateCopy  true
nullPlusNonNullIsNull   true
nullsAreSortedAtEnd false
nullsAreSortedAtStart   false
nullsAreSortedHigh  true
nullsAreSortedLow   false
storesLowerCaseIdentifiers  false
storesLowerCaseQuotedIdentifiers    false
storesMixedCaseIdentifiers  false
storesMixedCaseQuotedIdentifiers    false
storesUpperCaseQuotedIdentifiers    false
supportsANSI92EntryLevelSQL true
supportsANSI92FullSQL   false
supportsANSI92IntermediateSQL   false
supportsAlterTableWithAddColumn true
supportsAlterTableWithDropColumn    true
supportsBatchUpdates    true
supportsCatalogsInIndexDefinitions  false
supportsCatalogsInPrivilegeDefinitions  false
supportsColumnAliasing  true
supportsConvert false
supportsCoreSQLGrammar  true
supportsCorrelatedSubqueries    true
supportsDataDefinitionAndDataManipulationTransactions   true
supportsDataManipulationTransactionsOnly    false
supportsDifferentTableCorrelationNames  false
supportsExpressionsInOrderBy    true
supportsExtendedSQLGrammar  false
supportsFullOuterJoins  true
supportsGetGeneratedKeys    true
supportsGroupBy true
supportsGroupByBeyondSelect true
supportsGroupByUnrelated    true
supportsIntegrityEnhancementFacility    false
supportsLikeEscapeClause    true
supportsLimitedOuterJoins   true
supportsMinimumSQLGrammar   true
supportsMixedCaseIdentifiers    false
supportsMixedCaseQuotedIdentifiers  true
supportsMultipleOpenResults false
supportsMultipleTransactions    true
supportsNamedParameters true
supportsNonNullableColumns  true
supportsOpenCursorsAcrossCommit true
supportsOpenCursorsAcrossRollback   true
supportsOpenStatementsAcrossCommit  true
supportsOpenStatementsAcrossRollback    true
supportsOrderByUnrelated    false
supportsOuterJoins  true
supportsPositionedDelete    true
supportsPositionedUpdate    true
supportsRefCursors  false
supportsSchemasInIndexDefinitions   true
supportsSchemasInPrivilegeDefinitions   true
supportsSchemasInProcedureCalls true
supportsSelectForUpdate true
supportsStatementPooling    false
supportsStoredFunctionsUsingCallSyntax  false
supportsSubqueriesInComparisons true
supportsSubqueriesInExists  true
supportsSubqueriesInIns true
supportsSubqueriesInQuantifieds true
supportsTableCorrelationNames   true
supportsTransactions    true
supportsUnion   true
supportsUnionAll    true
usesLocalFilePerTable   false
usesLocalFiles  false

Here it wont work:

JDBC Driver CLASSNAME   com.ibm.as400.access.AS400JDBCDriver
JDBC Driver CLASSPATH   H:\Tools\JDBC\jt400.jar
getURL  jdbc:as400://xxx/mig
getDriverName   AS/400 Toolbox for Java JDBC Driver
getDatabaseProductName  DB2 UDB for AS/400
getDatabaseProductVersion   07.03.0000 V7R3m0
getDriverVersion    12.1
getUserName MIGDMIG
getDefaultTransactionIsolation  TRANSACTION_READ_UNCOMMITTED
isReadOnly  false
getResultSetHoldability 1
getMaxLogicalLobSize    2147483647
supportsRefCursors  false
supportsSchemasInTableDefinitions   true
supportsCatalogsInTableDefinitions  false
supportsCatalogsInDataManipulation  false
supportsSchemasInDataManipulation   true
getDatabaseMajorVersion 7
getIdentifierQuoteString    "
supportsStoredProcedures    true
supportsSavepoints  true
getCatalogSeparator .
supportsMultipleResultSets  true
getJDBCMajorVersion 4
supportsCatalogsInProcedureCalls    false
storesUpperCaseIdentifiers  true
getJDBCMinorVersion 2
getCatalogTerm  Database
getProcedureTerm    Procedure
getSchemaTerm   Library
supportsTableCorrelationNames   true
supportsNonNullableColumns  true
supportsExtendedSQLGrammar  false
supportsANSI92FullSQL   false
getDriverMinorVersion   1
supportsFullOuterJoins  true
getExtraNameCharacters  $@#
supportsPositionedDelete    true
supportsMinimumSQLGrammar   true
getDriverMajorVersion   12
supportsMixedCaseIdentifiers    false
storesLowerCaseIdentifiers  false
supportsSelectForUpdate true
supportsSubqueriesInQuantifieds true
supportsCorrelatedSubqueries    true
supportsGroupByUnrelated    true
getMaxBinaryLiteralLength   32739
supportsGroupByBeyondSelect true
supportsLikeEscapeClause    true
supportsCoreSQLGrammar  true
supportsSubqueriesInIns true
getMaxCharLiteralLength 32739
supportsOuterJoins  true
nullsAreSortedAtEnd false
supportsAlterTableWithDropColumn    true
supportsExpressionsInOrderBy    true
getSearchStringEscape   \
supportsOpenCursorsAcrossCommit true
supportsOrderByUnrelated    false
supportsSubqueriesInComparisons true
supportsMultipleTransactions    true
supportsANSI92EntryLevelSQL true
supportsANSI92IntermediateSQL   false
nullsAreSortedAtStart   false
storesMixedCaseIdentifiers  false
supportsAlterTableWithAddColumn true
nullsAreSortedHigh  true
supportsLimitedOuterJoins   true
allTablesAreSelectable  false
nullPlusNonNullIsNull   true
supportsColumnAliasing  true
supportsSchemasInProcedureCalls true
supportsPositionedUpdate    true
supportsSubqueriesInExists  true
allProceduresAreCallable    false
nullsAreSortedLow   false
usesLocalFilePerTable   false
storesLowerCaseQuotedIdentifiers    false
storesMixedCaseQuotedIdentifiers    false
storesUpperCaseQuotedIdentifiers    false
supportsNamedParameters true
getMaxColumnsInSelect   8000
getMaxIndexLength   2000
supportsStatementPooling    false
getMaxColumnsInOrderBy  10000
getMaxConnections   0
supportsBatchUpdates    true
getMaxStatementLength   1048576
getMaxColumnsInIndex    120
getMaxTableNameLength   128
supportsTransactions    true
getMaxUserNameLength    10
supportsMultipleOpenResults false
getMaxCursorNameLength  128
supportsGetGeneratedKeys    true
getMaxSchemaNameLength  128
doesMaxRowSizeIncludeBlobs  true
getMaxTablesInSelect    1000
getMaxColumnsInGroupBy  8000
locatorsUpdateCopy  true
getMaxCatalogNameLength 10
getClientInfoProperties ApplicationName 255     The name of the application currently utilizing the connection.
ClientUser  255     The name of the user that the application using the connection is performing work for.  This may not be the same as the user name that was used in establishing the connection.
ClientHostname  255     The hostname of the system the application using the connection is running on.
ClientAccounting    255     Accounting information.
ClientProgramID 255     Program identification information.

getMaxColumnNameLength  128
getMaxColumnsInTable    8000
getMaxProcedureNameLength   128
generatedKeyAlwaysReturned  false
supportsDataDefinitionAndDataManipulationTransactions   true
usesLocalFiles  false
supportsGroupBy true
supportsUnion   true
getSQLStateType 2
getRowIdLifetime    ROWID_VALID_FOREVER
supportsUnionAll    true
supportsConvert false
isCatalogAtStart    true
getMaxRowSize   32766
getMaxStatements    9999
supportsMixedCaseQuotedIdentifiers  true
supportsDifferentTableCorrelationNames  false
supportsIntegrityEnhancementFacility    false
supportsSchemasInIndexDefinitions   true
supportsOpenStatementsAcrossCommit  true
dataDefinitionCausesTransactionCommit   false
supportsSchemasInPrivilegeDefinitions   true
supportsCatalogsInPrivilegeDefinitions  false
supportsOpenCursorsAcrossRollback   true
supportsOpenStatementsAcrossRollback    true
dataDefinitionIgnoredInTransactions false
supportsStoredFunctionsUsingCallSyntax  false
supportsDataManipulationTransactionsOnly    false
autoCommitFailureClosesAllResultSets    false
supportsCatalogsInIndexDefinitions  false
getDatabaseMinorVersion 3

[Claus]

(I checked them - they are identically)

[John Eberhard]

What Java (JVM) version are you using for the two cases?

[Claus]

Not Working: JRE 1.8.0_192-b12
Working: JRE 1.8.0_131-b11

[John Eberhard]

The difference must be due to the JVM. Adding the zeros after the decimal point is the correct behavior. The JDBC driver uses the BigDecimal.toPlainString() method to convert to String when the ResultSet.getString(column) method is called. If you don't want trailing zeros, then the code to access the data should call ResultSet.getBigDecimal(column). Then the BigDecimal.stripTrailingZeros() method can be used to remove the trailing zeros.

[Claus]

You can close the Ticket. I found a different Option in our SQL-Tool which was not set correctly.