Recreating tables in SQL Server

[Dean_Eshleman]

Hi,  We are making our first attempt at modifying some existing tables in an SQL Server 2005 database.   Up to this point, all of our tables have been on the iSeries.   I have some questions about how we go about recreating the table to add some new fields.   Since Plex only generates the CREATE TABLE code, do people rename the existing table and then run the CREATE TABLE statement followed by some sql to move the data from the old table to the new table?   Or, do you use the ALTER TABLE statement that you craft by hand to add the new fields?   I'm guessing, people will say it depends on how extensive the changes are.  Second question.   We used SQL Management Studio to look at the SQL tables and then we generated the DDL for the table.   We tried to match this up with the code generated from Plex and as you might imagine, it doesn't match very well.   Here are some of the differences. The SQL Server code generates either NULL or NOT NULL for every field.   Plex doesn't generate anything except for one NOT NULL on a foreign key.   I thought this would be controlled by the optional vs required triples on the field, but I guess not.   I'm assuming NULL is probably the default if it isn't specified.   Is that true? One of the fields is an IDENTITY column.   The SQL source generates "MyFieldName   [int] IDENTITY(1,1) NOT NULL".   Can this be modeled in Plex? Anyone have any other gotcha's when dealing with SQL Server databases?   TIA  P.S. We are using Plex 5.5 currently. Message Edited by DeanE on 12-17-2009 03:24 PM [left]

[NICOLAS MAVROEIDIS]

Hi Dean,  Personally in AS/400 and SQL Server whenever I want to change an existing table structure I always use hand crafted ALTER TABLE statementsespecially when the table is in production, so I only need (as a precausion) to backup the existing data without the need to restore or copy them.  For specific DBMS things (like the identity) you would need to use the field 'DBMS script SRC' source code to modify the field's DDL Definition.Especially for the 'identity column', I would prefer NOT to use it, since I do not know how the Plex generated 'create instance' SQL Code will behavewith such a column (you can not insert values in such a field).  A few MS SQL Server hints:  .1.Data Types:update your 'own pattern layer' field definitions with the following:mycharacter   SQL format SYS       Char_Var
mynumeric       SQL format SYS       Numeric
mydate             low VAL                   1753-01-01  .2.Date ProcessingNote that all the 'date' fields are actually datetime fields so you might need to update your date comparison codefrom AS/400. e.g. If Filter >= Start And Filter Then ...Code...   might need tobecome:   If Filter >= Start And Filter +1.  .3.if you use OBASE: you will need to update your 'own pattern layer' definitions with the following:
MyBusiness entity.List attributes.Process some instances
At the : Edit Point   0 Initial instance not found
Place the statement :       Return        Otherwise the program might not perform as expected.  .4.Prefer to use Plex Functions of type SYS Database (Stored Procedures)
for massive server processing instead of Plex Server Programs (like Process some instances)and embed transaction processing inside them.  .5.Do the filtering on the server programs and prefer to use Exec Sql in order to 'create the result set',     avoid the Position GE statement and whenever you use it, use it along with the 'Maximum Recordset Size'   modifier.     (In the past I had posted a related message about this subject).  Best Regards, Nicolas Message Edited by Nicolas_Mavroeidis on 12-18-2009 10:57 AM [left]

[Legacy User]

Hi Dean,  For altering tables, I used to do as Nicolas, either manually creating ALTER TABLE scripts or just using Enterprise Manager to generate the script when manually changing the dev tables.   You obviously have to be aware of the underlying data if you are changing the type or length of a field and the effects that this might have on it and any indexes.   But generally I now use SQL Compare from Red Gate  to synchronise various development, QA and production database.   This will compare all the database structures for you, show what differences there are and allow you to chooes which to synchronise and generate the scripts for you to apply now or later.   SQL Data Compare allows you to do the same to the data.   So now I generate and build all database structures to an empty database and then use SQL Compare to synchronise, but also to capture changes that I've made directly to the dev database when tuning stored procedures - to make sure those changes get updated to the model.  For the DDL, I believe you are correct on NULL being the default and the optionality triple only affects programs which query this at gen time.   As Nicolas has said, you can use the 'DBMS script SRC' for the identity column, but I'll admit that I have not used this where Plex is doing inserts and updates - as weel as not inserting to this field, I'll imagine attempted updates to this field may also fail (the SQL Plex generates updates all fields regardless of whether they have actually changed or not).  In addition to Nicolas' hints I'd add that I make extensive use of Select Where - the where clause is generated based on the restrict, position variables, specified filters, plus hand coded SQL fragments.   We also often have entities scoping views that I have not figured out how to model in Plex which do plenty of complicated joins and aggregations, case statements etc  - the views are not built, but the entity has the associated scripts attached.  Make friends with SQL Profiler, it really helps to see the SQL you are executing against the database when you are getting no rows back.   You can execute the SQL in Management Studio to work out where it has gone wrong.  If performance is in issue learning to understand the excecution plan of the SQL statements is a must also.

[Dean_Eshleman]

Thanks for the tips everyone.   I'll keep them in mind as we proceed.    

[Anon Anon]

Hi Dean,  In our organization we promote SQL Server's table modifications via SQL scripts that are generated based on the PLEX-created files using the SQL Server tools to create/drop objects (right-mouse click over the objects  in the SUMMARY  pane). These scripts include in some cases the steps required to preserve the data in the modified files by making a backup copy, moving the data from the original to the backup file, dropping of the original file, creation of the modified file, and restoring of the data  Attached is a sample file that exemplifies these steps.Hope this helps,  LucioPLEX WORLD

Attachment(s)

JEMS5009912-LG-AdvancedPocketJEMS_EmailOnPFOfficer-SQLPre...zip   1 KB 1 version

[Dean_Eshleman]

Hi Lucio,  Thanks for the information.   I'll check out the file you attached.  

[Anon Anon]

Just refreshing this.
Anybody since dealt with handling the IDENTITY columns for SQL Server for INSERTS and UPDATES.

I am coming to the conclusion that I may just need to create my own 'exec SQL' to support this type of functionality. This should be patternable. Any other feedback gratefully received. Or I might just take JohnRhodes view on the application layer on the surrogate post and throw some grunt at it and leave the surrogate generation in the application. But I will lose the advantage of the DBMS handling other (non application inserts)

Thoughts?

Kind Regards.
Lee.

[Anon Anon]

Lee,
If you are using transactions based process and performance is paramount importance I would highly recommend NOT use the Plex's default surrogate behavior. It has too many issues that would make it difficult to work with in multi-threaded environment. DBMS like SQL Server are pretty good with Identify fields and my recommendation would be to utilize them. We did not have too many scenarios for the Identify fields but where ever we had, we pretty much ended up using Stored Procedures to do the inserts. Yes, it is painful to build but in the long run it would save you lot of headaches.

Srini