Database Collation Utility

Topics: Possible Errors When Running Scripts

Starting with WebFOCUS Release 8.2 Version 02, the db_collation.sh script is available and located in the install_directory/ibi/WebFOCUS82/utilities/dbupdate/collation/ directory.

This utility is supported with MS SQL Server and MySQL databases and enable you to change the database collation from case insensitive to case sensitive.

The script provides the available options:

check_cs_collation

• Checks if the database collation is case sensitive.
• Prompts the user to use the database configured in install.cfg (select Y) or use a different database instance (select N).
• Prompts for the database repository ID and password.
• Prompts for the connection information, if the database configured with the installation is not being used.

Output example:

[2017-11-21 17:08:53,729] INFO stdout - Starting collation_tool(check_cs_collation) process ...
[2017-11-21 17:08:54,278] OFF stdout - Database collation is NOT case sensitive or does not meet WebFOCUS requirements
Or
[2017-12-13 12:41:11,117] INFO stdout - Starting collation_tool_install(check_cs_collation) process ...
[2017-12-13 12:41:11,831] OFF  stdout - Database collation is case sensitive
[2017-12-13 12:41:11,831] INFO stdout - Done
Database IS case sensitive

collation change

• Changes the database collation to the best matched CS collation.
• Prompts the user to use the database configured in install.cfg (select Y) or use a different database instance (select N).
• Prompts for the database repository ID and password.
• Prompts for the connection information, if the database configured with the installation is not being used.

Output example:

[2017-12-05 13:26:53,714] INFO stdout - Starting collation_tool_install(collation_change) process ...
[2017-12-05 13:26:55,081] OFF stdout - Collation changed.

get_current

• Retrieves database collation.
• Prompts the user to use the database configured in install.cfg (select Y) or use a different database instance (select N).
• Prompts for the database repository ID and password.
• Prompts for the connection information, if the database configured with the installation is not being used.

Output example:

[2017-11-21 09:53:58,559] INFO stdout - Starting collation_tool_install(get_current) process ...
[2017-11-21 09:53:59,403] OFF stdout - Database collation: 'Latin1_General_CS_AS'

list_cs_collations

• Lists all case sensitive collations supported by the database.
• Prompts the user to use the database configured in install.cfg (select Y) or use a different database instance (select N).
• Prompts for the database repository ID and password.
• Prompts for the connection information, if the database configured with the installation is not being used.

Output example:

…
"SQL_Latin1_General_CP1251_CS_AS","Latin1-General, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 105
on Code Page 1251 for non-Unicode Data","1251"
"SQL_Latin1_General_CP1253_CS_AS","Latin1-General, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 113
on Code Page 1253 for non-Unicode Data","1253"
"SQL_Latin1_General_CP1254_CS_AS","Turkish, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 129
on Code Page 1254 for non-Unicode Data","1254"
"SQL_Latin1_General_CP1255_CS_AS","Latin1-General, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive
…

list_cs_compatible_collations

• Retrieves the list of CS collations compatible with the specified collation.
• Prompts the user to use the database configured in install.cfg (select Y) or use a different database instance (select N).
• Prompts for the database repository ID and password.
• Prompts for the connection information, if the database configured with the installation is not being used.

Output example:

[2017-11-21 10:29:31,566] INFO stdout - Starting collation_tool_install(list_cs_compatible_collations) process ...
COLLATION_NAME,COLLATION_DESCRIPTION,CHARACTER_SET/CODE_PAGE
------------------------------------------------------------
"Japanese_90_CS_AS_KS_WS_SC","Japanese-90, case-sensitive, accent-sensitive, kanatype-sensitive, width-sensitive, supplementary characters","932"

Note: To run the utility on UNIX, open a UNIX shell, navigate to the directory with the script, run db_collation.sh, and enter a selection to proceed.