----------------------------------------------------------------------------
SYBASE TECHNICAL NEWS
Volume 7, Number 10 October 1998
----------------------------------------------------------------------------
Introduction
This issue of Sybase Technical News contains new information about your
Sybase software. This newsletter is intended for Sybase customers. All
issues of Sybase Technical News are included on the Technical Library CD and
web pages.
----------------------------------------------------------------------------
In This Issue
----------------------------------------------------------------------------
Tip of the Month
TechNotes:
* Using Data-Only-Locked Tables with Peoplesoft Products
http://techinfo.sybase.com/css/techinfo.nsf/DocId/ID=20319
Doc Summary:
This TechNote lists some PeopleSoft tables in PeopleSoft Financials and
PeopleSoft that benefit from Sybase datarows or datapages locking. HRMS
Payroll that, based on PeopleSoft benchmarking, benefit from datarows
locking or datapages locking.
* Using the float Datatype
http://techinfo.sybase.com/css/techinfo.nsf/DocId/ID=20313
Doc Summary:
This is a synopsis of what floating point is, environmental factors,
and when best to use it.
* Resolving "Failed to load unicode conversion DLL libntlog.dll" with EBF
8075
http://techinfo.sybase.com/css/techinfo.nsf/DocId/ID=47847
Doc Summary:
After installing ESD#6 (EBF 8075) for Adaptive Server 11.5.1 you will
receive an error message: kernal "Failed to load unicode conversion DLL
libntlog.dll". The server will still boot and run, the only thing affected
is the NT event logging.
Other Useful Information
Subscription
----------------------------------------------------------------------------
Tip of the Month: Understanding How alter table Affects Status Bits
----------------------------------------------------------------------------
In Adaptive Server Enterprise 11.5.x, when you use alter table to add a
varchar column, the status bit remains as 0x0080 (PG_FIXED) in existing
pages. However, if you create a table with a varchar column, the datapage's
status bit is not 0x0080 (PG_FIXED).
This occurs because adding a column with alter table does not affect the
datapages. Instead, alter table adds a row to syscolumns, and the column is
placed at the end of the rows. When the last varchar column of the row is
null, the column is not stored so it appears to be a fixed row. Therefore,
the status bit 0x0080 (PG_FIXED) in existing pages resets only when you
insert a new row with a non-null value in the last varchar column.
Run dbcc page to display the datapage's status bits. The syntax is:
dbcc page (database_ID, page_ID)
TechNotes
-----------------------------------------------------------------------------
USING Data-Only-Locked TABLES WITH PEOPLESOFT PRODUCTS
-----------------------------------------------------------------------------
This TechNote lists some tables in PeopleSoft Financials 5 and PeopleSoft
HRMS Payroll 7 that, based on limited PeopleSoft benchmarking, benefit from
datarows locking or datapages locking.
Determining which tables to alter
When you install Adaptive Server 11.9.x, the installation program sets the
tables to allpages locking. You must alter the tables to change the locking
scheme. The PeopleSoft installation guide and upgrade information contain
scripts to convert all tables to datarows locking; however, it may not
always be useful to do so.
Determining which tables to alter should be based on your concurrency needs.
Your environment's needs may differ, depending on
* the mix of modules running concurrently
* site reporting requirements
* customization
* which PeopleSoft version you are running
* data variations
You may need to perform additional benchmarking to determine which tables in
your environment should be in allpages versus data-only locking. If you do
not want to tune your environment, keep your database in the recommended
PeopleSoft configuration, all datarows locking.
Note: Switching from allpages locking to data-only locking is expensive in
terms of I/O cost. For information about locking, see New
Functionality in Sybase Adaptive Server Enterprise 11.9.2
Tables that benefit from locking changes
This TechNote is for sites using the PeopleSoft Payroll 7 and the PeopleSoft
Financials 5 modules: general ledger, asset management, accounts payable,
and accounts receivable. This TechNote contains:
* PeopleSoft Financials 5 tables to alter from allpages locking to
datarows and datapages
* PeopleSoft HRMS Payroll 7 tables to alter from allpages locking to
datarows
* alter table PeopleSoft Financials 5 script
* alter table PeopleSoft HRMS Payroll 7 script
Not all PeopleSoft tables appear on these lists, and there are many
PeopleSoft modules. For help with PeopleSoft tuning and administration,
contact PeopleSoft Global Support Center (in the United States,
1-800-477-5738) or the PeopleSoft Customer Connection website. See also
Appendix B, "Enabling Row Level Locking," of the "PeopleSoft 7.5
PeopleTools: Installation and Administration for Sybase" guide in the
PeopleSoft Books On-Line documentation set.
About the PeopleSoft benchmarking
Information in this TechNote is based on PeopleSoft/Sybase standard
benchmarking for these modules.
The Payroll benchmark kit ran on-cycle PeopleSoft Payroll 7 in an isolated
batch environment.
The Financials benchmark kit information is an online test of the four
PeopleSoft 5 modules --general ledger, asset management, accounts payable,
and accounts receivable-- with very limited batch and reporting running in
the background.
For descriptions of these benchmarks, see the PeopleSoft Customer Connection
website.
PeopleSoft Financials 5 tables
Based upon PeopleSoft benchmarking, Sybase recommends using datarows locking
for the following tables:
* PS_JRNL_HEADER
* PS_OPR_DEF_TBL_AM
* PS_VOUCHER
* PS_RUN_CNTL_AR
* PS_TSE_JLNE_FLD
* PS_BUS_UNIT_TBL_FS
* PS_JRNL_LN
* PS_JRNL_CF_BAL_TBL
Based upon PeopleSoft benchmarking, Sybase recommends using datapages
locking for the following tables:
* PS_BUS_UNIT_TBL_AR
* PS_LEDGER
* PS_LEDGER_TMP
* PS_LEDGER_TMP2
* PS_COST
* PS_ASSET_tmp
* PS_ASSET
* PS_VOUCHER_LINE
* PS_ASSET_ACQ_DET
* PS_PAYMENT_ITEM
* PS_VCHR_ACCTG_LINE
* PS_BOOK
* PS_ASSET_LOCATION
* PS_TSE_JHDR_FLD
* PS_CUST_CONTACT
* PS_PYMNT_VCHR_XREF
* PS_PAYMENT_TBL
* PS_PAYMENT
* PS_INSTALLATION_AR
* PS_CUST_CONVER
* PS_CUST_CONVER_DTL
PeopleSoft HRMS Payroll 7 tables
Based upon PeopleSoft benchmarking, Sybase recommends using datarows locking
for the following tables:
* PS_PAY_EARNINGS
* PS_PAY_DISTRIBUTN
* PS_PAY_OTH_EARNS
* PS_PAY_CHECK
* PS_BOND_LOG
* PS_GENL_DEDUCTION
* PS_PAY_PAGE
* PS_PAY_LINE
* PS_PAY_TAX
* PS_PAY_DEDUCTION
* PS_PAY_SPCL_EARNS
* PS_PAY_GARNISH
* PS_TAX_BALANCE
* PS_DEDUCTION_BAL
* PS_EARNINGS_BAL
* PS_LEAVE_ACCRUAL
* PS_CHECK_YTD
* PS_GARN_BALANCE
* PS_PAY_MESSAGE
* PS_PAY_GARN_OVRD
* PS_PAY_CTX_OVRD
* PS_PAY_IMP_RUNCTL
* PS_PAY_CAL_BAL_ID
* PS_PAY_FORM_TBL
* PS_PRCSSYSTEM
* PS_PAY_CONF_RUNCTL
* PS_PAY_CALC_RUNCTL
* PS_PAY_CALENDAR
* PS_PAYSHEET_RUNCTL
Alter Table scripts
This section contains scripts to alter tables for PeopleSoft products. You
may need to change the database name in the script.
* PeopleSoft Financials 5 script
* PeopleSoft HRMS Payroll 7 script
PeopleSoft Financials 5 script
use FINSERV
go
alter table PS_BUS_UNIT_TBL_AR
lock datapages
go
alter table PS_LEDGER
lock datapages
go
alter table PS_LEDGER_TMP
lock datapages
go
alter table PS_LEDGER_TMP2
lock datapages
go
alter table PS_JRNL_HEADER
lock datarows
go
alter table PS_COST
lock datapages
go
alter table PS_ASSET_tmp
lock datapages
go
alter table PS_OPR_DEF_TBL_AM
lock datarows
go
alter table PS_ASSET
lock datapages
go
alter table PS_VOUCHER_LINE
lock datapages
go
alter table PS_VOUCHER
lock datarows
go
alter table PS_ASSET_ACQ_DET
lock datapages
go
alter table PS_PAYMENT_ITEM
lock datapages
go
alter table PS_VCHR_ACCTG_LINE
lock datapages
go
alter table PS_BOOK
lock datapages
go
alter table PS_RUN_CNTL_AR
lock datarows
go
alter table PS_TSE_JLNE_FLD
lock datarows
go
alter table PS_ASSET_LOCATION
lock datapages
go
alter table PS_BUS_UNIT_TBL_FS
lock datarows
go
alter table PS_TSE_JHDR_FLD
lock datapages
go
alter table PS_CUST_CONTACT
lock datapages
go
alter table PS_JRNL_LN
lock datarows
go
alter table PS_PYMNT_VCHR_XREF
lock datapages
go
alter table PS_PAYMENT_TBL
lock datapages
go
alter table PS_PAYMENT
lock datapages
go
alter table PS_JRNL_CF_BAL_TBL
lock datarows
go
alter table PS_INSTALLATION_AR
lock datapages
go
alter table PS_CUST_CONVER
lock datapages
go
alter table PS_CUST_CONVER_DTL
lock datapages
go
PeopleSoft HRMS Payroll 7 script
Change the database name in the script before running the script.
use
go
alter table PS_PAY_EARNINGS
lock datarows
go
alter table PS_PAY_DISTRIBUTN
lock datarows
go
alter table PS_PAY_OTH_EARNS
lock datarows
go
alter table PS_PAY_CHECK
lock datarows
go
alter table PS_BOND_LOG
lock datarows
go
alter table PS_GENL_DEDUCTION
lock datarows
go
alter table PS_PAY_PAGE
lock datarows
go
alter table PS_PAY_LINE
lock datarows
go
alter table PS_PAY_TAX
lock datarows
go
alter table PS_PAY_DEDUCTION
lock datarows
go
alter table PS_PAY_SPCL_EARNS
lock datarows
go
alter table PS_PAY_GARNISH
lock datarows
go
alter table PS_TAX_BALANCE
lock datarows
go
alter table PS_DEDUCTION_BAL
lock datarows
go
alter table PS_EARNINGS_BAL
lock datarows
go
alter table PS_LEAVE_ACCRUAL
lock datarows
go
alter table PS_CHECK_YTD
lock datarows
go
alter table PS_GARN_BALANCE
lock datarows
go
alter table PS_PAY_MESSAGE
lock datarows
go
alter table PS_PAY_GARN_OVRD
lock datarows
go
alter table PS_PAY_CTX_OVRD
lock datarows
go
alter table PS_PAY_IMP_RUNCTL
lock datarows
go
alter table PS_PAY_CAL_BAL_ID
lock datarows
go
alter table PS_PAY_FORM_TBL
lock datarows
go
alter table PS_PRCSSYSTEM
lock datarows
go
alter table PS_PAY_CONF_RUNCTL
lock datarows
go
alter table PS_PAY_CALC_RUNCTL
lock datarows
go
alter table PS_PAY_CALENDAR
lock datarows
go
alter table PS_PAYSHEET_RUNCTL
lock datarows
go
----------------------------------------------------------------------------
USING the float DATATYPE
----------------------------------------------------------------------------
Most of us are familiar with integers, which map directly onto words in
computer memory. Floating-point format is a way to use those words to
represent non-integer numbers with fractional parts. For example, 3.142 is
stored in two pieces, the raw number "3142" and "1 place". The result
"3.142" is called "floating point" because the decimal point floats or moves
to accommodate value changes during expression evaluations. By contrast, in
fixed-point arithmetic, the number of decimal places remains constant.
You can use the float datatype to assign a floating-point format to the data
in a table column. Keep in mind, however, that float is an approximate
numeric datatype, as defined by ANSI standards. It stores slightly imprecise
representations of real numbers as binary fractions at the hardware level.
The accuracy of float datatypes is limited by the number of bits used to
represent the abscissa, the part raised to the power represented by the
mantissa. When the representations are displayed, printed, transferred
between hosts, or used in calculations, they lose precision.
Note: For approximate equality, compare two floating-point values. Check
that the difference between them is small compared to the size of
the numbers, rather than look for exact equality.
*When to Avoid Using float
Do not use the float datatype where absolute precision is required. This
section gives examples of types of situations to avoid.
*Financial Applications
Programmers writing financial applications often mistakenly use
floating-point datatypes to represent monetary values, such as dollars and
cents. Doing so results in mysterious rounding errors where the total of a
column is slightly different from the exact arithmetic sum of all integer
values that represent the currency amounts.
For exact representation of decimal fractions, use one of the following
datatypes, as appropriate to your needs:
* numeric and decimal datatypes. These two datatypes are identical,
except that you can use numeric types with a scale of 0 for the
IDENTITY column. For details on these exact numeric datatypes and their
optional parameters, see the Transact-SQL User's Guide.
* money and smallmoney datatypes. These two datatypes are accurate to one
ten-thousandth of a monetary unit. For display purposes only, they
round up to two decimal places.
Remember, monetary values entered with E notation are interpreted as
float, which can cause the entry to be rejected or to lose precision
when stored as money or smallmoney.
*Loop Indexes
Do not use floating-point variables as loop indexes. For example, you would
not use:
DECLARE counter float;
counter = 0;
while (counter <1) loop
counter = counter + 0.1;
end loop
Adding 0.1 ten times to a floating-point variable initialized to zero would
not give a final value of 1.0. The result is very close to 1.0, but not
exactly equal. Instead you would use:
DECLARE counter integer;
counter = 0;
while (counter <10) loop
counter = counter + 1;
end loop
IEEE Format
The double precision datatype, one of the approximate numeric datatypes for
storing floating-point numbers, is the most accurate server floating-point
representation. A double precision floating-point number is stored in 8
bytes.
In UNIX and Alpha environments, SQL Server and Adaptive Server use the IEEE
format for floating-point numbers, a total of 64 bits for any value: 1 bit
for the sign, 11 bits for the exponent, and the other 52 bits for precision.
Two floats must differ by one (2 to the 52nd power), which is about 17
decimal digits, so that their representation differs.
Some decimal numbers, however, cannot be represented exactly by binary
numbers. For example, the floating-point number 1.20003 may be interpreted
as 1.20029999999999930082594801206. Notice that the sixth decimal digit
differs, although the stated accuracy of 17 decimals is true.
IEEE is fairly accurate to within 16 decimal places. A floating-point
value's mantissa is a fractional value, impossible to represent adequately
with a binary number. For values that are not exact multiples of 2^-n power,
precision can be lost. Also, conversion of ASCII representations of float
into IEEE format is inaccurate in the last part of the number. For example,
0.000000000000987654320999999935 becomes 0.000000000000987654321000000137.
Note: For SQL Anywhere and Adaptive Server Anywhere a double precision
floating-point number can represent about 15 digits with reasonable
accuracy. Beyond that, values may be subject to round-off error.
*Alpha Platforms
SQL Server and Adaptive Server on Alpha platforms use IEEE single precision
(S-floating) and IEEE double precision (T_floating) for the 4-byte and
8-byte floating datatypes, respectively.
For single precision (S-floating), the Alpha approximation of 0.1 is
1.60000002384185791015625 * 2^-4 = 1.60000002384185791015625 / 16 =
0.100000001490116119384765625.
----------------------------------------------------------------------------
Resolving "Failed to load unicode conversion DLL libntlog.dll" with EBF 8075
----------------------------------------------------------------------------
After installing ESD#6 (EBF 8075) for Adaptive Server 11.5.1 you will
receive an error message: kernal "Failed to load unicode conversion DLL
libntlog.dll". The server will still boot and run, the only thing affected
is the NT event logging.
There are two workarounds available for this:
1.If you are not using one of the following character sets:
sjis, deckanji, eucjis, eucgb and euccns. (Japanese and European character
sets)
Copy or rename libunic.dll to libntlog.dll and that will remove the error
message when the server is booting.
2.If you are using one of the following character sets:
sjis, deckanji, eucjis, eucgb and euccns. (Japanese and European character
sets)
Download libntlog.dll from the powersoft ftp site.
FTP: Connect to ftp.powersoft.com. Login as annonymous, and user your email
address as the password. Change directory to pub/private/perm. Switch to
binary mode, and get libntlog.dll (See details listed below)
WEB: Point your browser's location at the following address:
ftp://ftp.powersoft.com/pub/private/perm/libntlog.dll
Install a copy of libntlog.dll in your \sybase\dll directory
*Downloading from the Powersoft FTP Site
To protect customer confidentiality, the Powersoft FTP site is 'blind'. This
means that you cannot use the ls command to view a list of the files in a
directory. It also means that some
graphical FTP utilities, which depend on being able to display a list of
files, cannot be used to download files from the Powersoft FTP site.
Because the Powersoft FTP site is 'blind', the recommended technique is to
use command line FTP to download files. Please follow these steps:
1.On the NT 4 desktop, press the Start button, select Programs, and then
select Command Prompt to open an NT Command Prompt window. On the NT 3.51
desktop, go to
Program Manager, select the Main program group, and select MS DOS Command
Prompt.
2.Type ftp and press enter to start the NT command line FTP utility.
3.Type open ftp.powersoft.com and press enter at the ftp> prompt.
4.Type anonymous and press enter at the User(psa.ftp.powersoft.com: (none)):
prompt.
5.Enter your email address in name@organziation format and press enter at
the password: prompt. Note that your password will not be echoed on the
screen.
6.Type binary and press enter.
7.Type cd pub and press enter.
8.Type cd private and press enter.
9.Type cd perm and press enter.
10.Type get libntlog.dll and press enter.
11.When the download is finished, Type quit and press enter to exit from the
NT command line FTP utility.
----------------------------------------------------------------------------
Other Useful Information
--------------------------------------
* Certification Reports
http://techinfo.sybase.com/css/techinfo.nsf/Certification
Browse by certification
* Product Availability
http://techinfo.sybase.com/css/techinfo.nsf/New+Products
These Alerts feature new Sybase products which became available for
sale during the time span listed on the Alert. Included are
instructions on actions you need to take and an explanation of
abbreviations.
* End-of-Life/End-of-Support
http://techinfo.sybase.com/css/techinfo.nsf/eolproducts
Collection of end-of-support letters to customers having contracts for
various products on different platforms. Each letter describes the
end-of-support plan for these products.
* Error Messages
http://techinfo.sybase.com/css/techinfo.nsf/Topic
* Migrating from Sybase Adaptive Server 11.5
http://techinfo.sybase.com/css/techinfo.nsf/DocID/ID=34982-01-1150-01
A guide to help you migrate from 4.x, 10.x and 11.0.x, including
templates and checklists.
* What's New
http://techinfo.sybase.com/css/techinfo.nsf/Whats+New
Browse the most recently released documents.
For frequent updates, visit the Sybase Technical Library on the World Wide
Web at http://techinfo.sybase.com.
Electronic Case Management and Electronic Software Delivery access remains
limited to Sybase support contacts who are registered with SupportPlus
Online Services. ECM and ESD requires SSL web browser support. ECM and ESD
users also may access the Technical Library from behind the firewall in the
usual manner, allowing them to retain the security benefits of using SSL
with their web browser.
Subscription Information
--------------------------------------
Send comments to technews@sybase.com. To receive this document by regular
email, send email to majordomo@sybase.com, and in the body of your message
put subscribe inews-technews-full. For a summary version by email, put
subscribe inews-technews-summary. No subject line is necessary.
Staff
Principal Editor: Betsy Brazy
Production Editors: Rodney Esperanza, Leigh Ann Hussey
Contributors: Kalpana Aravamuthu, Shashikant "Shashi" Bhandari, Kevin
Kreutsweiser, Frank de Lange, Mike Maas, Vinaye Misra, Sujoy Paul, Michael
Shields, Carol Talbeck
Disclaimer
--------------------------------------
No express or implied warranty is made by Sybase or its subsidiaries with
regard to any recommendations or information presented in Sybase Technical
News. Sybase and its subsidiaries hereby disclaim any and all such
warranties, including without limitation any implied warranty of
merchantability of fitness for a particular purpose. In no event will Sybase
or its subsidiaries be liable for damages of any kind resulting from use of
any recommendations or information provided herein, including without
limitation loss of profits, loss or inaccuracy of data, or indirect special
incidental or consequential damages. Each user assumes the entire risk of
acting on or utilizing any item herein including the entire cost of all
necessary remedies.
----------------------------------------------------------------------------
--------------------------------------------------
If you ever want to remove yourself from this mailing list, you can send
mail to "majordomo@sybase.com" with the following command in the body of
your email message:
unsubscribe inews-technews-full
If you ever want to add yourself from this mailing list, you can send
mail to "majordomo@sybase.com" with the following command in the body of
your email message:
subscribe inews-technews-full