Common issues w/ Powerbuilder-Sybase applications.
Current Powerbuilder version: 6.5.1 (2/7/01)

Use of Autocommit: I have advocate examples for setting it to true
or false.  The parameter is SQLCA.AUTOCOMMIT.  Issues:

- if false (default), Powerbuilder issues an implicit begin tran as soon
as the previous transaction is committed.  This means that idle apps can
leave open transactions in the trans log for a long time (permanently if
the app crashes w/o cleaning up behind itself).  This has two effects
on the SQL server: open transactions affect the dump tran commands we
typically run hourly (can't dump tran past an open tran in the log).  Also
the periodic checkpoint process can't correctly checkpont the database
and write dirty pages to disk.  The solution to this is to code in a commit
in the "idle action" and configure the idle action to fire often (every 5-10

Sidenote: You cannot use a #temp table within a stored procedure when
autocommit is false. If you want to use #temp table then you must
create it BEFORE turning autocommit to false...otherwise you can use
a "working" temporary table (one that exists as a real object in a
database other than tempdb). 

If false, your transaction count may be off unexpectedly; be aware if
coding against this variable.

- if true, powerbuilder programmers have to specifically initiate begin
tran commands when they want them.  Having autocommit to true 
sometimes causes unintended open transactions to commit themselves 
against the intentions of the programmers.  System 11 introduces private
log cache which buffers transaction log writes until there's actual data
modified, thus the above issue (open trans then app crash) will have no
effect on the real transaction log.

- Another option is to modify the behavior of the front end to buffer
all changes to the local memory until a user submits the changes; at that
point open the transaction, submit changes, commit, and close connection.

- Whether or not you use autocommit in the frontend, transaction control 
is still key in powerbuilder apps.

See this link:
(Document ID 42077 in Sybase's techinfo web site).  This discusses
Transaction processing and mentions Powerbuilder's Autocommit feature.

- Use of spids versus stored procedures: powerbuilder programmers tend
to avoid the use of stored procedures, instead opting to generate all
the sql at the front end.  this leads to performance problems, plus
additional unnecessary network load.  Also, stored procedures are far
better performance-wise: they're pre complied, pre optimized and pre
planned to execute.

Powerbuilder Developers (understandibly) have a tendency to write
a lot more SQL code through the .Update() function during development.
This is because its faster to test and debug for them, they're often
not skilled at writing SPs, or they don't have onsite DBAs or SQL
coders to write the procs for them.  However, I recommend an audit
of the code at a post-development time and a conversion of the more
complicated code to Stored Procedures.

- network packet size: if using powerbuilder to retreive large amts of
data, set this much higher than the default 512k.  Be careful though;
setting this value can crush pre System10 open client front ends.

(not really Powerbuilder issue: issue w/ PC-Unix box connectivity)
- tcp keepalive parameter; to time out dead powerbuilder connections
on Suns, type ndd -get /dev/tcp tcp_keepalive_interval to see what its 
current setting is.  Default on Sun is 7200000 milliseconds, which is 2 
HOURS...meaning that the tcp device won't timeout dead connections for 2 
hours...and you all know how often PC clients suddenly crash.  The solution 
is to lower this value to like 2 minutes, or 120000 milliseconds.
(On HP: nettune is sun equiv of ndd.  nettune -s tcp_keepidle 
to set it (it might be tcp_keepfreq.  On AIX, "no" command?)

- (not really a Powerbuilder issue but an OpenClient issue).
Sybase client applications get a recurring Character set error regarding
cp850 when connecting to the server.  This is typically because Openclient
defaults to the cp850 character set during installs (see c:\sybase\locales\
locales.dat).  The solution is to either edit this locales.dat file
and change the default character set to be something more common
(like iso_1) or to get into sybinit on the server and add the cp850 
character set.

- PB Data manipulation oddities: Powerbuilder has problems working with 
tables with multiple text fields IF you set the [Database] string
incorrectly in your pb.ini file.
DBMS=SYC Sybase System 10 or 11


Setting this will also lead to strange results when reading just one text
field, identities, numeric column datatypes, and features Sybase has added
since 4.9.2, since "DBMS=Sybase" seems to default to 4.9.2 behaivor.
(Note: this is also the DBMS to use when building a Database profile 
within the application)

- Powerbuilder also can't modify data in tables without having a primary key 
set (via sp_primarykey) or a clustered unique index present.  
- Powerbuilder displays NULLs and a series of blanks the same way (no 
affect on the data but you can't tell graphically the difference)

- Embedded newlines in varchar and text fields: where as most front ends
recognize "\n," powerbuilder wants "\r\n" instead.

- 11.5 has a bug (fixed in EBF8015) that causes an error when using tables
with a time stamp column (unsupported datatype error).

- When creating data windows in powerbuilder, be sure to confirm that 
Powerbuilder has created joins correctly.  Often times, POwerbuilder selects
the wrong keys to join table on (it just scans down the key list for the
first key of the datatype you're joining on).  Not solely a Sybase issue.

- To populate the "program_name" in sysprocesses (or the hostname so that
sp_who shows your application), you need to add the following lines to
your pb.ini file:
DbParm = AppName='application name',host='hostname '

- Powerbuilder must be restarted if any table change has occurred 
once the database connection has been established.  I creates a
database table profile upon login and cannot refresh.

- Powerbuilder has a very very nice feature called Pipeline, which can
handle many different DBA tasks that would normally be rather a
rather tedious, bcp out and in or select into process.  I've seen this 
function used to:
x drop attributes from a table
x port data from one server to another, table by table
x change attribute types (from float to int)

Example Stored Proc call in Powerbuilder

SQLCA.AutoCommit = FALSE 
DECLARE spName PROCEDURE FOR cost_db.dbo.spName Using SQLCA; 
EXECUTE spName; 
IF SQLCA.SqlCode <> 0 THEN 
  MessageBox("Stored Procedure Error", SQLCA.SqlErrText) 
CLOSE spName; 
SQLCA.AutoCommit = TRUE 

Q: How do i use Powerbuilder as a database data GUI front end?
- Click on "Configure ODBC" button after starting app; make sure you have an ODBC
- Click on "DB Profile" select your database and hit "connect"
- click on "Database" and you can select tables to display graphically