How to ALTER a table created with SELECT INTO LOCAL TEMPORARY TABLE...?

2010-03-12T18:13:10Z

SA 11.0.1.2376

When I create a local temporary table by means of a SELECT INTO LOCAL TEMPORARY TABLE, I am not able to alter that table since ALTER TABLE returns a "table not found" error. Is this by design? If so, why?

(Business case: I often "copy" remote data into temp tables and prefer to omit the declaration of all columns and types – call it lazyness. However, I usually have to define PKs and the like afterwards.)

Example:

select * into local temporary table LT
from systable

select * from LT
-- works

alter table LT add test_col int null
-- returns "table LT not found"

How do I get a copy of procedure profiling statistics?

2010-03-12T17:21:30Z

v10.0.1

When profiling a procedure I don't seem to be able to copy the line by line stats. You can get the procedure text but not the numbers. I wonder if they can be extracted from a system table?

(seem to have lost my OpenID!!)

Database Recovery: What Would You Do If...

2010-02-26T17:59:11Z

Title isn't that great, so feel free to change.

Basically, I will describe an issue we had this week. I want to get responses from everyone on what you would have done in the scenario. I won't post what we did at first, but will later on.

Scenario:
A 35GB SQL Anywhere 9 database with 1 remote site that receives all data, and around 190 remote sites that receive limited data. Two assertion failures in one week. One, which was an index issue. The second, a little more severe - an invalid page.

If anyone would like more details, let me know.

Additional Information:

SQL Remote
The backup was from two days prior as the one from the night before was lost due to the failure.
9.0.2.3527
Assertion failed: 201501 (9.0.2.3527) Page for requested record not a table page or record not present on page
Only the consolidated
Replication runs as a service on the server, and by a user triggering it on the laptops, so any number of sites could have replicated at any time.

Return Query Result In Different Language?

2010-03-11T23:29:09Z

We have been asked if it is possible to translate some reports to different languages on the fly (from an EN based database).

So my question is, can I translate data stored as ENGLISH into some other language on the fly through the use of a procedure/view/etc?

MySQL proxying ...

2010-03-10T08:18:35Z

Hi, How can I define a MySql "Remote Server" on Linux ? Can I define some proxy table to INSERT data on MySql server ? Thanks.

Legacy collation matching

2010-03-11T15:51:35Z

We unloaded 40 or so databases to Sybase 11 using the "-an" flag and it preserved our legacy collation sequence of "850". However, when we use dbinit to create new databases we are unable to find a collation sequence that matches this legacy "850" collation. Neither "IBM850" nor "850LATIN1" match the sorting properties of the old "850" collation.

We found a collation sequence named "internal - code page 850, multilingual" that appears to have the same sorting properties as the legacy "850" collation.

Can anyone confirm that the two collations are equivalent for sorting purposes?

How can I improve this stored function performance?

2009-12-01T18:26:57Z

create function bludata.onlyNumber(in val long varchar)
returns long varchar
begin
  declare sTemp long varchar;
  declare iCount integer;
  set iCount=1;
  set sTemp='';
  while iCount <= length(val) loop
    if substring(val,iCount,1) in( '0','1','2','3','4','5','6','7','8','9') then
      set sTemp=sTemp+substring(val,iCount,1)
    end if;
    set iCount=iCount+1
  end loop;
  return sTemp
end

Actually, set sTemp='' consumes 13.6% of exec time and if line 86.4%
I need to improve because this stored function is executed more than 10k times/minute.

Sql COUNT Performance Question

2009-11-17T11:37:51Z

Structure of Example table:

Id, Integer (PK)
Name, Varchar(100)
Description, Text

I need to know if exists difference in performance between:

SELECT COUNT(*) FROM Example;

and

SELECT COUNT(Id) FROM Example;

Or does not exists differences?

Vendor Announcement: Eric Farrar's Getting Started with SQL Anywhere 11 Webcast

2010-03-02T09:28:58Z

Are you new to SQL Anywhere 11? Have you recently downloaded the Developer Edition? Join Eric Farrar, Product Manager for SQL Anywhere, to learn how you can get started with the industry-leading database management and data movement technologies.

Date: Tuesday, March 16, 2010

Times: 7am PST / 10am EST / 3pm GMT / 4pm CET or

11am PST / 2pm EST / 7pm GMT / 8pm CET

This presentation includes a guided tour of the technologies, tools and resources that make SQL Anywhere so popular among developers. Learn how you can build powerful database applications for frontline environments. This webcast will present detailed information regarding:

    * SQL Anywhere 11 components and usage

    * Installation, connecting to the database, creating a database

    * Database tools, samples and documentation

    * Where to go from here?

Click here to register.

Is it advantageous to create FOREIGN KEYs after data is loaded?

2010-03-09T11:49:48Z

When rebuilding a database with a RELOAD.SQL file, the rebuild process generally creates foreign keys after the data is loaded.

Is that a performance advantage when compared to including the foreign key definition right in the table creation step? – Or is that just in order to prevent FK violations during the data loading phase?

Or asked more generally:

Will the "quality" of the index automatically built for a FK be different when the FK definition is before or after the table is filled (with LOAD TABLE)?

If the order of steps is important, would it then be as useful to defer PRIMARY KEY definitions as well (what DBUNLOAD does not)?

Current system: 8.0.3.5574, also relating to 11.0.1 and 12 beta (I'm aware that PK/FK indices are handled differently in 10+ databases).

SA11 Log all commands

2010-03-10T12:34:59Z

How can I log all commands the clients are sending to database server?

I wanna to "see" those commands to know how entity framework 4.0 is generating sqls and maybe try to change ling expressions.

Making a database recommendation: ASA vs ASE

2010-03-08T23:37:16Z

Hi:

I am working on performance tuning a two-tier Java Swing application which connnects to an Sybase ASA database (version 10.0.1).

We are currently doing a profiling of the application across both the Java layer and also the Database layer (using Sybase Central) to identify the cause of the performance issues.

One of the goals of the exercise is to find out if Sybase ASE is a better fit than ASA (or vice-versa). I need some help in making this case - I have searched for documentation on the web and couldnt find anything which compares the two.

Are you aware of any documentation which will help me compare and contrast Sybase ASA vs. ASE? Any help on this topic would be greatly appreciated. Thanks!

How can I use INSERT...ON EXISTING when I can't use the primary key as match?

2010-01-29T11:08:38Z

I have a "results" base table where I have stored data from different source tables.

It's the situation where

a simple view would be better suited but the select is way to complex to perform well and
even a materialized view seems to complex to formulate (at least for me).

Therefore I decieded to use a base table and have several insert...select statements to fill this table. All well (and all classic SQL).

Now, I have to update rows in the results table. (Inserting rows is easy by just checking which ones are missing.)

It's quite easy to find out which rows should get updated by using an EXCEPT statement.

However, my idea to use INSERT...ON EXISTING UPDATE failed because the results table has its own PK (default autoincrement), which is totally unrelated to the source tables, as they are referenced by different FK columns. INSERT...ON EXISTING however daes a primary key lookup. That won't do without a fitting PK. Unlucky me.

What can I do in SA 11 to solve the problem?

Is "LOAD STATISTICS" useful when extracting remote databases?

2010-03-08T13:10:16Z

Hi all,

a standard DBXTRACT reload.sql file contains a bunch of LOAD STATISTICS statements, typically based on a particular page size. AFAIK, they are used to refine the table statistics of the database that is to be built.

Two related questions:

Are these statistics based on the particular data extract or on the consolidated database's data?
If based on the consolidated's data, how senseful is this, as each remote will often only include a small part of all data and might have totally different variance? Or asked differently: How "bad" is it to omit these LOAD STATISTICS statements?

I'm just upgrading a custom extraction script, and the LOAD STATISTICS stuff is the only part that I cannot code myself:) So if it's not too useful, I'd like to omit it.

Regards Volker

(Currently using ASA 8.0.3.5574 but also testing with SA 11.0.1 and SA 12 beta.)

How can I use a subquery to limit my WHERE clause in an UPDATE?

2010-03-05T22:53:24Z

I have a customer table (8 million records or so) and I want to set each customer record with the value of the latest order date from the orders table.

I'm in a replication environment, so I don't want to update records needlessly by setting nulls to nulls and dates to the same date.

Here's the table layout (vastly simplified);

CREATE TABLE "rhiner"."customer" (
    "Customer_id" integer NOT NULL DEFAULT global autoincrement,
    "Last_order_date" date NULL,
    PRIMARY KEY ( "Customer_id" )
);

 CREATE TABLE "rhiner"."orders" (
        "order_id" integer NOT NULL DEFAULT global autoincrement,
    "order_date" date NULL,
    "customer_id" integer NULL,
    PRIMARY KEY ( "order_id" )
 );

ALTER TABLE "rhiner"."orders" 
    ADD NOT NULL FOREIGN KEY "customer" ( "customer_id" ) 
    REFERENCES "rhiner"."customer" ( "Customer_id" );

Here's what I want to to... Update only those customers that have an order on file that is later than the last recorded order date on the Customer table.

My attempt fails... well, it updates everything, but it updates EVERYTHING -- not just the records that need updating. (And it generates a bajillion replication messages needlessly.)

update rhiner.customer c
set last_order_date = 
  (select max(order_date)  
  from rhiner.orders 
  where orders.customer_id = customer.customer_id
       )

I've tried various things to pull the max(order_date) out the subquery to use in a WHERE clause for the customer, but I've had no luck. But somehow, the results of the subquery need to be part of the where clause. Well, unless I scrap the subquery idea and do a JOIN.

Any help is appreciated!

Using SQLA 9.0.2.3850, replicating via dbremote

SQLA Announcement: UPDATE - Check out the "links" link

2010-03-05T17:02:17Z

Check out the "links" link in the grey linkbar (look up, to the right: ... | faq | links

The "links" page will grow in time, and be rearranged as necessary... post all of your suggestions as comments or answers here!

SQLA Announcement: Relaxed reputation requirements are now in effect

2010-02-03T10:02:40Z

Apparently "Beta 6" of the StackExchange software used for this website (SQLA) is now in effect, and one of the new features is called the "Bootstrap Mode" where reputation points aren't quite as necessary for new SQLA users... in other words, new users can actually get something done right away.

Bootstrap Mode makes a lot of sense for the SQLA community, and it will probably be left on permanently.

The full description of Bootstrap Mode appears below. I hope it's working as advertised, maybe a new user can tell me if that's the case.

Oh, and another (apparent) new feature: "Email/Password Authentication" in addition to that insanely-confusing-and-error-prone OpenID stuff. I've enabled Email/Password Authentication as well as Bootstrap Mode... someone please tell me if it makes a difference.

For more information about StackExchange Beta 6 see http://meta.stackexchange.com/questions/3793/whats-new-in-beta-6

StackExchange Beta 7 is "coming soon"... it's the version that is expected to have the "database export" facility, also known as "end-user backup"... without the ability for us (i.e., me) to back up the question-and-answer content stored in SQLA, this website ain't never gonna go "live".

Just so you know... Ma.gnolia.com this ain't <g>

Bootstrap Mode helps to shape the community before the site is released to its full audience.

Reputation requirements are relaxed in bootstrap mode. All users can:

* Post Questions
* Post Answers
* Comment
* Create Tags
* Re-Tag Questions
* Vote

You'll earn reputation normally, but you won't be hampered by it.

SQLA Announcement: The FAQ has been updated

2010-02-28T16:14:37Z

Getting ready for prime time... "Welcome Visiting Newsgroup Users" material has been added.

http://sqla.stackexchange.com/faq

Vendor Announcement: Sybase Techwave 2010 August 9 to 11 in Washington DC

2010-03-05T07:16:37Z

An extra day this year... here's the official Techwave website.

According to Chris Kleisath the SQL Anywhere team will be there.

What comes first, the chicken (RSOE) or the egg (MobiLink Server)?

2010-02-23T22:37:22Z

we have a Relay Server/MobilLink environment and peridically the MobiLink Server stops responding (we will see the database connection has dropped, or something). It would seem that restarting that one service should solve the problem, however I find I have to restart ML Server, then restart RSOE, the restart ML Server again to get everyone to talk again.

it is hard to find a dead chicken in the server room to wave over the keyboard, so I am hoping someone can recommend a more reliable restart procedure.

Thanks, Bill

Vendor Announcement: UPDATED - Sybase Developer Days Worldwide Seminar Tour 2010

2010-02-18T11:14:17Z

Sybase Developer Days Worldwide Seminar Tour 2010

Read about the "tracks" including SQL Anywhere 12, then register.

Europe

Monday, April 19, 2010, Milan, Italy
Tuesday, April 20, 2010, Wiesbaden, Germany
Wednesday, April 21, 2010, Amsterdam, Netherlands
Thursday, April 22, 2010, London, UK
Friday, April 23, 2010, Madrid, Spain
Tuesday, April 27, 2010, Stockholm, Sweden
Wednesday, April 28, 2010, Paris, France

North America

Monday, May 3, 2010, Boston, USA
Tuesday, May 4, 2010, New York, USA
Thursday, May 6, 2010, Houston, USA
Tuesday, May 11, 2010, Los Angeles, USA
Wednesday, May 12, 2010, San Francisco, USA

Isn't it time for another benchmark?

2010-03-01T09:08:20Z

It's been a year and a half since SQL Anywhere 11 made it into the Top Ten TPC-C by Price/Performance and it's slipped to 8th place now...

Isn't it time to crank up Version 12 and get it down to .45 USD per tpmC?

[cue victorious King Kong voice]

Poll: Is it OK for vendors to post questions?

2009-11-21T12:06:40Z

I'm asking the question, but I also have an opinion: Yes, vendors should be welcome to post announcements, conduct polls, and whatever else they would like to do... within reason.

This includes iAnywhere Solutions, third-party product vendors, professional services consulting companies, user groups, anyone who offers products and/or services related to SQL Anywhere.

These posts should be tagged "vendor" and the title must start with the word "Vendor" as in "Vendor announcement: ...", "Vendor poll: ..." and so on. They will either start out that way, or they will get edited that way.

Here's the "within reason" part: If it looks like an advertisement, it will be deleted. If it looks like a press release, it will be deleted.

If it looks anything at all like anything you see on Sys-Con, it will be deleted.

And it has to be related to the stuff that ships in the box labelled "SQL Anywhere". That raises the question, what about products like PowerDesigner and InfoMaker? Yes, as long as the posting has something to do with SQL Anywhere specifically, not just in a general sense because the product happens to work with SQL Anywhere. I'm thinking about all the architectural astronaut stuff that PowerDesigner does... nope, no posts about that unless it specifically relates to SQL Anywhere.

Now, in case anyone thinks that this website should be entirely non-commercial, that's not going to happen. This is a community website, and communities include businesses. If money makes you uncomfortable, then this community may not be for you.

Having said all that, this is a poll, and Free Speech is in effect.

Poll: Rethinking tags

2010-01-26T12:18:43Z

The current thinking on tags is that they should be used to classify questions, NOT to provide search keywords... Google can do searching just fine without tags.

There have also been suggestions to put tag-like prefixes in the Titles themselves:

Product Suggestion: ...

Vendor Announcement: ...

Poll: ...

I'm thinking that tags are sufficient in some cases; e.g., you don't need to be told it's a "Product Suggestion:" in the title, tagging it "product-suggestion" is probably sufficient.

At the other end, the "Poll:" prefix might be a good idea, along with the "poll" tag, to make sure folks notice the question.

The "Vendor Announcement:" prefix is open for debate. Is the "vendor-announcement" tag sufficient?

Maybe when we actually get some vendor announcements from/about companies other than Sybase/iAnywhere, we can decide.

Poll: How do you version control your schema?

2010-02-24T00:59:19Z

We're a long time SQL Anywhere+Delphi development shop. A lot of our developers have come up with their own simple implementations of how to manage the version of a SQL Anywhere database schema with its related delphi application(s). However we're wanting to standardise how we do this, so thought we'd use this fantasic resource.

By manage, I mean:

For this particular version of our application in our version control system this is the version of the database schema it is designed to work with.
Using version control to track the change of a table, view, sproc, etc. over time.
Match changes to the schema to the related changes to the application. E.g. we added purchase ordering to our application, what specific changes to the database were required.

So, what do other SQL Anywhere developers use to version control their schema?

How do I use OUTPUT with dynamic filename?

2010-02-26T20:35:25Z

I'm running SQLA 9.0.2.3850

Using ISQL, I want to dynamically create a file name, then write the file based on a query. Here is my code...

BEGIN 

  DECLARE @output_filename char (100);
  SET @output_filename = 'C:\TEMP\JUNK_' ||  DATEFORMAT(CURRENT TIMESTAMP, 'YYYYMMDD') || '.txt';

  SELECT TOP 10 * FROM sys.systable order by table_id;

  OUTPUT to @output_filename format ascii;

END

I get "Syntax error near 'OUTPUT'" when I run this. I can't find anything in the docs that says this should not be allowed. And I have to use the BEGIN/END statements in order to use variables, right??

Why is after update trigger not fired on changes of a before update trigger SQLA 10

2010-02-02T12:19:58Z

Since we upgraded our Database from 9.0.2. to 10.0.1. one of my trigger constructs is broken.

When a user changes a value in a purchase order line (testchild) the purchase order (testmain) record was updated in version 9.0.2.

In 10.0.1 this updated is skipped.

create table testmain (
   PO_ID        char(12)       not null,
   PO_GOODSNET  numeric(12,4)  not null default 0,
   constraint PK_TESTMAIN primary key (PO_ID)
);

create table testchild (
   POLINE_ID        char(12)           not null,
   PO_ID            char(12)           not null,
   POLINE_PRICE     numeric(12,4)      not null,
   POLINE_QUANTITY  numeric(12,4)      not null,
   POLINE_SUM       numeric(12,4)      not null default 0,
   constraint PK_TESTCHILD primary key (POLINE_ID)
);

create TRIGGER "testchild_CRNCY_TBIU" BEFORE INSERT, UPDATE
ORDER 1 ON "testchild"
REFERENCING NEW AS NewRow
FOR EACH ROW
-- calculates the sums of a line and convert the price and the sum
into the system lead currency
BEGIN
  set NewRow.POLINE_SUM = NewRow.POLINE_PRICE * NewRow.POLINE_QUANTITY;
END;

create trigger "testchild_UpdateMain" AFTER insert, update, delete
order 99
ON "testchild"
referencing new as NewRow old as OldRow    
for each row
   -- Update corresponding denormalized purchase order columns
begin
   declare cPO_ID  REPL_ID;
   Declare nSum    Amount;

   if inserting or updating then
       set cPO_ID = NewRow.PO_ID;
   else
       set cPO_ID = OldRow.PO_ID;
   end if;

   -- Update PO_GOODSNET
   if deleting or inserting or UPDATING('POLINE_SUM') then
       select  sum(POLINE_SUM)
       into    nSum
       from    testchild
       where   PO_ID = cPO_ID;

       update  testmain
       set     PO_GOODSNET = nSum
       where   PO_ID = cPO_ID;
   end if;    
end;

TEST CASE

insert into testmain ( PO_ID ) values ( 1 );

insert into testchild( POLINE_ID, PO_ID, POLINE_PRICE, POLINE_QUANTITY )
values ( 'CHILD1', 1, 1.50, 1000 ) ;

update testchild set POLINE_PRICE = 1 where POLINE_ID = 'CHILD1';

What does the NodeAddress connection property 'NA' mean?

2010-02-26T09:14:19Z

The Help for 11.0.1.2276 says that "When the client and server are both on the same computer, an empty string is returned" but that's not completely correct.

When the client is an EVENT, it returns 'NA'. Does that mean 'Not Applicable'?

Likely answer: "Gosh, what do you think, Breck?" <g>

And here is another stupid question: Does it return 'NA' for SERVICE connections as well? ( I don't have any of those that last long enough to see :)

SELECT * FROM sa_conn_properties() WHERE PropName = 'NodeAddress';

Number,PropNum,PropName,PropDescription,Value
1000000107,193,'NodeAddress','Client node address','NA'
1000000021,193,'NodeAddress','Client node address','NA'
177,193,'NodeAddress','Client node address',''

How to avoid execution of DatabaseStart Event

2010-02-25T12:06:44Z

When your Database contains a EVENT of type DatabaseStart and you are able to crash the Database Engine during startup with your fabulous code.

How would you fix the database to come up again.

I have seen that -gf is implemented to disable triggers. Is there somthing like -xyz to disable events from the command line ?

Think of the posibilities. Implement a bogus DatabaseStart event and when you bring up your server 3 Month later it is blocked. And you did not try to start you backup database ;-)

Why am I only seeing the date portion of a timestamp value?

2010-02-20T13:05:39Z

(the question says it all)

Top Questions - SQLA