MSSQL List of Table's Referencing Objects

Similarly as we did in our Oracle past post, we now propose a simple but useful MSSQL query, that let us to know which objects (for example, stored procedures) reference a specified table (or any other object):


SELECT DISTINCT
   referenced_schema = d.referenced_schema_name
   , referenced_object_name = d.referenced_entity_name
   , referenced_object_type = o1.type_desc
   , referring_object_schema = s.name
   , referring_object_name = o.name
   , referring_object_type = o.type_desc
FROM
   sys.sql_expression_dependencies d
   INNER JOIN sys.objects o ON d.referencing_id=o.object_id
   INNER JOIN sys.schemas s ON o.schema_id=s.schema_id
   INNER JOIN sys.objects o1 ON d.referenced_id=o1.object_id
WHERE
   --d.referenced_entity_name = 'table_name'
   d.referenced_entity_name like '%object_name%'
ORDER BY
       referenced_schema_name
;



But how can MSSQL obtain such a list of referenced table objects, contained inside the code of a stored procedure? Well, everytime you CREATE or ALTER a stored procedure, the compiler dynamically creates a list of syntatic objects, internally implemented as hash table; this list contains also all the code's referenced table, and it´s therefore used to keep the system tables and metadata updated.

LIKE is not always enough

Let´s imagine a situation in which we have, as input, a column s.namelist from a table s containing a comma-separated list of values:

Fabio,Andrea,John,Sara,Sarah...

This could be, for example, a dump from a CSV file or an Oracle External Table, inside a database staging area.

Now suppose we have to perform a filtering operation basing on the corresponding value of another column, s.name; in particular, we would like to identify -and insulate- all the names from our comma-list, whose name is the same in our corresponding s.name column. So, if we have a row like:

  ... | Smith | ... | Sara, Karl, John, Smith, Bill, Smith, Hoppen,... | ....

we wanna obtain:

 | Smith | Smith, Smith |

or just the entire name list.

(this doesn´t seem having much sense, but a similar situation could easly occour in many DWH or data integration scenarios).

Starting with a simple query statement (in this case T-SQL under MSSQL) like this:

select

       s.name

       , a.namelist  LIKE ( '%|' + UPPER(RTRIM(LTRIM(b.name)))

  +'|%' ) )

Doesn´t work correctly. What happen if we have two names like "Sarah" and "Sara", while filtering for "Sara"? With the LIKE operator, we would catch also "Sarah".

The solution is to clearly break and delimiatet the comma list names before performing any additional filtering/denormalization operation, through the use of the REPLACE operator

select

       s.name

       , '|' + REPLACE(UPPER(LTRIM(RTRIM(a.namelist))),' ', '|') + '|'  LIKE ( '%|' + UPPER(RTRIM(LTRIM(b.name)))

  +'|%' ) )

We will then obtain a clear, delimited list of names as |Sara|Sarah|Pippo|... in which we can easly apply the LIKE operator with the name explicitely delimited by the "|" as input

The RTRIM/LTRIM, and the UPPER operators will help us on avoiiding the classical data quality problems involving fields coming from source systems as names, addresses, etc...

Oracle APEX Login Troubles

Oracle Application Express is a web-based, scalable development framework based on the Oracle Database, available from the Oracle 9.2. Oracle APEX follows a thin-client logic, demanding most of the processing and validation operations to the Oracle DBMS itself.

Oracle APEX is also available in the free XE version of the Oracle Database.
   
While providing an Oracle APEX solution to a small set of users, it's of course a good idea to deploy it in the "Embedded PL/SQL Gateway" way, simply following step by step the guidlines as specified in the Oracle official documentation.

In this way however, after a successful installation and while logging into your new APEX system, you could face the presence of the a dialog box asking a password for the "XDB" user:

The server xxx at XDB requires a username and password.

   
followed by the same request, but for the "APEX" user:

The server xxxxx at APEX requires username and password

This is why the Oracle XDB HTTP Server is by default configured to use the same TCP port of the APEX Listener, the 8080. If you do not want to use a different port or disable the XDB HTTP Server completely, here is a simple workaround.

First of all, we unlock the XDB user:

ALTER USER xdb ACCOUNT UNLOCK;

...and we set a new password:

ALTER USER xdb IDENTIFIED BY xdb_new_pwd;

We make the same for the ANONYMOUS user (as described by the Oracle documentation) and for the APEX_PUBLIC_USER.

Oracle Automatic Startup at Boot Time - Linux Script

If you are frequently working with the Oracle technology, in most of the cases your Oracle system will run over a Linux machine, for example based on a Red Hat compatible distribution (Red Hat Enterprise, Fedora, or CentOS).

In fact, if your purpose is to test, develop or evaluate a solution based on the Oracle stack, you can freely rely on a virtual machine based on the free Oracle Virtualbox (in case, do NOT forget to install the VirtualBox Guest Additions!), the CentOS distribution, and the Oracle Database Enterprise Edition (or Standard, or even XE - it depending on your purposes).

Unfortunately, by default both Oracle Enterprise and Standard Edition don' t set any startup script in the /etc/init.d/ directory at installation time. No panic: we can create a custom one and manually deploy it.


First of all, we check our /etc/oratab file, whose row follows the following syntax:

istance_sid:oracle_home:[Y|N]

The "oratab" file is automatically created by the Oracle installer, and it's used by the "dbstart" and "dbshut" scripts to figure out which database istances have to be start up or shut down. In particular:

• istance_sid: System ID (SID) of the desired oracle instance;
• oracle_home: ORACLE_HOME directory associated to the specified istance;
• [Y|N] simply indicates if the istance should automatically start at boot time (Y="yes", N="no").

Obvioulsy, we set as "Y" every database instance we wanna automatically to be started at boot time.

For example, a production /etc/oratab file could look like something like this:

orcl:/opt/oracle/product/11.2.0/dbhome_1:N
dev:/opt/oracle/product/11.2.0/dbhome_1:Y
test:/opt/oracle/product/11.2.0/dbhome_1:Y
prod:/opt/oracle/product/11.2.0/dbhome_1:Y

Now we can create our new init script. We create a new, empty file called "oradb" and we add the following lines:

#!/bin/sh
# chkconfig: 345 20 80
# description: counter daemon
# processname: counter
# /etc/rc.d/init.d/oracle
# Description: Automatically starts and stops the Oracle database and the listeners.

Note: the line "# chkconfig: 345 20 80" is absolutely necessary to make the script compatible with the Red Hat service management subsystem and should not be skipped!

The following code implements the "body" of our init script:

case "$1" in
  start)
        echo -n "Starting Oracle Databases: "
        echo "----------------------------------------------------" >> /var/log/oracle
        date +"! %T %a %D : Starting Oracle Databases as part of system up." >> /var/log/oracle
        echo "----------------------------------------------------" >> /var/log/oracle
        su - oracle -c dbstart $ORACLE_HOME >> /var/log/oracle
        echo "...done."
        echo -n "Starting Oracle Listeners: "
        su - oracle -c "lsnrctl start" >> /var/log/oracle
echo "...done."
        echo ""
        echo "----------------------------------------------------" >> /var/log/oracle
        date +"! %T %a %D : Finished." >> /var/log/oracle
        echo "----------------------------------------------------" >> /var/log/oracle
        touch /var/lock/subsys/oracle
        ;;
  stop)
        echo -n "Shutting Down Oracle Listeners: "
        echo "----------------------------------------------------" >> /var/log/oracle
        date +"! %T %a %D : Shutting Down Oracle Databases as part of system down." >> /var/log/oracle
        echo "----------------------------------------------------" >> /var/log/oracle
        su - oracle -c "lsnrctl stop" >> /var/log/oracle
        echo "...done."
        rm -f /var/lock/subsys/oracle
        echo -n "Shutting Down Oracle Databases: "
        su - oracle -c dbshut $ORACLE_HOME >> /var/log/oracle
echo "...done."
        echo ""
        echo "----------------------------------------------------" >> /var/log/oracle
        date +"! %T %a %D : Finished." >> /var/log/oracle
        echo "----------------------------------------------------" >> /var/log/oracle
        ;;
  restart)
        echo -n "Restarting Oracle Databases: "
        echo "---------i-------------------------------------------" >> /var/log/oracle
        date +"! %T %a %D : Restarting Oracle Databases as part of system up." >> /var/log/oracle
        echo "----------------------------------------------------" >> /var/log/oracle
        su - oracle -c dbshut $ORACLE_HOME >> /var/log/oracle
        su - oracle -c dbstart $ORACLE_HOME >> /var/log/oracle
        echo "...done."
echo -n "Restarting Oracle Listeners: "
        su - oracle -c "lsnrctl stop" >> /var/log/oracle
        su - oracle -c "lsnrctl start" >> /var/log/oracle
echo "...done."
        echo ""
        echo "----------------------------------------------------" >> /var/log/oracle
        date +"! %T %a %D : Finished." >> /var/log/oracle
        echo "----------------------------------------------------" >> /var/log/oracle
        touch /var/lock/subsys/oracle
        ;;
  *)
        echo "Usage: oracle {start|stop|restart}"
        exit 1
esac

As you may notice, we make use of the Oracle standard "dbstart" ("dbshut") and "lsnrctl" utilities.

Note: in order to make this script correctly work, the "dbstart", "dbshut" and "lsnrctl" utilites must be present in the PATH environmental variable! You can check it by typing the command:

# env | grep PATH

If the PATH environmental variable is not correctly set up, you can manually do it using:

# PATH:$PATH:oracle_utils_path; export PATH

You can locate the utilities using the command:

# updatebd; locate dbstart

or

# find / -name dbstart

Adding this line to the /etc/profile file will make the correct PATH variable also available at boot time.




Now you must deploy the script in the /etc/init.d/ directory. The script should be own by the "root" user:

# chown root:root /etc/init.d/oradb

and should be granted with the "775" permission:

# chmod 775 /etc/init.d/oradb

Eventually, to make Oracle automatically start at boot time type:

# chkconfig --add oradb

Restart your system, set the ORACLE_SID to your database SID name, and test the script it via SQLPLUS, TOAD, or SQL Developer. You can (as root) also manually call the script:

• # /etc/init.d/oradb start: to make the database istances start;
• # /etc/init.d/oradb stop: to make them stop;
• # /etc/init.d/oradb restart: to make them restart.

Precision of a Timestamp in Oracle

Sometimes in Oracle you could need to explicitly specify the precision of a TIMESTAMP field. In case, you should use the "DUAL" pattern.

Let' s give an example:

SELECT to_char(CURRENT_TIMESTAMP, 'YYYYMMDD HH24MISS.FFN') FROM DUAL;

where "N" refers to the desired precision (number of digits after the comma). We use an explicit cast using the "TO_CHAR" function, since we wanna see the real output - and not the one setted in the IDE environment (TOAD, SQLPLUS, or SQL Developer).

So, for istance:

SELECT to_chat(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF3') FROM DUAL;

will result in something like:

20120326 114347.215

and

SELECT to_char(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF9') FROM DUAL;

in:

20120326 114551.183876000

Oracle Stored Procedures with Custom Java Code

Togheter with the Oracle´s official Structured Query Language PL/SQL, and the open-source statistical analysis language bundled in the Oracle Data Mining package, R, we also have the possibilty to use and integrate another great technology in our database: Java, the world's number one programming language in enterprise environments.

The usage of Java inside stored procedures in pretty much simple: you just need to create a standard stored procedure "linking" to your custom java code. Here is a short example:

CREATE OR REPLACE FUNCTION    your_function_name( p_arg1 IN VARCHAR2) RETURN NUMBERAS   LANGUAGE JAVA   NAME 'your_java_function_name(java.lang.String...) return integer';

public static int your_function_name(String separator, String args) {     ...your java code...} 

Getting the List of Referring Tables to a Table

If you wanna know, in Oracle, all referring tables to a certain table here is a simple query:

SELECT  bs.OWNER , bs.TABLE_NAME , bs.CONSTRAINT_NAMEFROM  USER_CONSTRAINTS bs , USER_CONSTRAINTS rf WHERE  bs.CONSTRAINT_TYPE='R'  AND rf.TABLE_NAME='YOUR_TABLE_NAME' AND bs.R_CONSTRAINT_NAME=rf.CONSTRAINT_NAME;

If, in addition, you wanna also know the referring column names:

SELECT  bs.OWNER , bs.TABLE_NAME , cl.COLUMN_NAME , bs.CONSTRAINT_NAMEFROM  USER_CONSTRAINTS bs , USER_CONSTRAINTS rf  , USER_CONS_COLUMNS clWHERE  bs.CONSTRAINT_TYPE='R'  AND rf.TABLE_NAME='T_CL_KUNDE_D' AND bs.R_CONSTRAINT_NAME=rf.CONSTRAINT_NAME AND bs.CONSTRAINT_NAME=cl.CONSTRAINT_NAME;

Using the same script of the previous post, we can implement a fast script for disabling all the foreign key contraints referring to the desired table:

SET SERVEROUTPUT ON;DECLARE v_stmt VARCHAR2(255);BEGIN
  FOR i IN
(
SELECT
bs.CONSTRAINT_NAME "constraint_name"
, bs.TABLE_NAME "table_name"
FROM
USER_CONSTRAINTS bs
, USER_CONSTRAINTS rf
, USER_CONS_COLUMNS cl
WHERE
bs.CONSTRAINT_TYPE='R'
AND rf.TABLE_NAME='your_table_name'
AND bs.R_CONSTRAINT_NAME=rf.CONSTRAINT_NAME
AND bs.CONSTRAINT_NAME=cl.CONSTRAINT_NAME
)
  LOOP
   v_stmt := 'ALTER TABLE your_table_name DISABLE CONSTRAINT ' || i.CONSTRAINT_NAME;
   EXECUTE IMMEDIATE v_stmt;

   dbms_output.put_line('disabled: ' || i.constraint_name || ' from table: ' ||  i.table_name);
  END LOOP;
END;
/

Temporary Disabling the Foreign Keys in Oracle

Foreign Keys are the most widely used constraint types in OLTP systems, since they ensures that a value of the referencing column cannot contain a value that does not exist in the corresponding column of the referenced table. Foreign Keys do not guarantee absolute data quality: rather, they guarantee... referential integrity, which is a good starting point for obtaining a good level of data quality and reliability.

OLTP systems are always accessed in a "transactional" way by multiple users: each data entry is inserted row by row, and should therefore be controlled  one by one, togheter with the help of the other database constraints (or the application level filters, if the system is ill-designed).

In DWH environments, however, things are different. First, the data integrity is - or should be- guaranteed by the automatic ETL process; exceptions, error or any data inconsistency has to be automatically managed - and reported - direct at etl level. Sometimes one of the goal of the ETL process is also to find data anomalies and problems: this can be part of the DWH structure itself, for example with the use of an Audit Dimension or Fact.

Second, the presence of explicit foreign keys (logically we ALWAYS have referencial integrity between facts and dimensions) load performance is brutally degraded: a cross-table check is triggered for every insert/update/delete statement, resulting into an unacceptable overhead.

Third, during the developement of a DWH System operations like table truncating and changing are frequent, thus causing headaches when making DML or DDL changes.

If you are not the Project Manager, and you cannot decide to drop -once for all- the explicit and annoying foreign keys from the database, you can temporary disable them by using a simple procedure similar to the one contained in the following anonymous block:

SET SERVEROUTPUT ON;DECLARE v_stmt VARCHAR2(255);BEGIN FOR i IN (SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME='YOUR_TABLE_NAME' AND CONSTRAINT_TYPE='R') LOOP v_stmt := 'ALTER TABLE ' || i.TABLE_NAME || ' DISABLE CONSTRAINT ' || i.CONSTRAINT_NAME; EXECUTE IMMEDIATE v_stmt; dbms_output.put_line('disabled: ' || i.CONSTRAINT_NAME || ' from table: ' ||  i.TABLE_NAME); END LOOP;END;/

Similarly, to re-enable the foreign keys:

SET SERVEROUTPUT ON;DECLARE v_stmt VARCHAR2(255);BEGIN FOR i IN (SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME='YOUR_TABLE_NAME' AND CONSTRAINT_TYPE='R') LOOP v_stmt := 'ALTER TABLE ' || i.TABLE_NAME || ' ENABLE CONSTRAINT ' || i.CONSTRAINT_NAME; EXECUTE IMMEDIATE v_stmt; dbms_output.put_line('enabled: ' || i.CONSTRAINT_NAME || ' from table: ' ||  i.TABLE_NAME); END LOOP;END;/

As always, do not forget the backslash "/" at the end of the anonymous block.

Date Comparison in Oracle

If you wanna compare (equal) two dates in Oracle, you can' t simply use the "=" operator in the query clause. Instead, you can perform a simple string comparison through the TO_CHAR function:

SELECT   dt1   , dt2FROM   src_tableWHERE TO_CHAR(dt1, 'YYYY-MM-DD')=TO_CHAR(dt2, 'YYYY-MM-DD');

Working with Oracle Sequence Objects in Talend Open Studio

In case you need a column that contains unique, sequentially generated numbers, you can realize autonumber fields (also known as "auto-increment" fields) in RDMBS engines like Oracle and PostgreSQL by using sequence objects. This is often the case of the primary surrogate keys in DWH environments.

In Oracle, a sequence object is a separate structure specifically created to generate sequential values; it can be efficiently and concurrently accessed by more than one process at time, and the same sequence can be used for one or more tables.

If you are using an ELT tool like Oracle Warehouse Builder, the access and increment of the sequence object into a mapping is pretty much easy: we simply drag and drop the sequence object itself, and connect the "NEXTVAL" field to the desired column of the target table.

Mapping of a sequence object in Oracle Warehouse Builder.

With our favourite Data Integration tool, however, things are a bit more complicated. Suppose we don' t wanna delegate the "autoincrement" logic to the ETL process, and that we simply wanna insert new rows into one table, letting the RDBMS manage the sequential logic by itself. In this case, we would obviously make use of a sequence object:

DROP  SEQUENCE seq_name;

CREATE  SEQUENCE seq_nameSTART WITH 1 INCREMENT BY 1 MINVALUE 1 NOCACHE NOCYCLE NOORDER;

The problem is that, with Talend Open Studio, we don't have a way to directly access the sequence object inside the ETL flow - and we can't therefore implement any incremental logic at ETL level; neverthless, the sequence object doesn't simply increment by itself - you have to access it in some way, at application level.

We can' t directly access an Oracle sequence object in Talend Open Studio.

The solution is to implement a trigger object: each row in the target table will trigger an increment of the sequence object, whose value will be stored in the "autoincrement" column itself: 

CREATE OR REPLACE TRIGGER  trg_nameBEFORE INSERT ON  table_name FOR EACH ROW WHEN     (new.autoincr_col IS NULL)  BEGIN    SELECT       seq_name.NEXTVAL    INTO         :new.autoincr_col    FROM        DUAL;  END;/

The trigger object makes slower the loading process - as a matter of fact each insert row triggers a different operation. However, it perfectly works in case of small amount of insert data.

Do not forget to add the backslash "/" at the end of the trigger creation script.

Sending emails... from the Oracle database

ELT tools like Oracle Warehouse Builder are poor of predefined components for integrating the Data Warehouse with the rest of the world.

For istance, suppose that you are using Oracle OWB and your ETL/Data Integration process is required to notify you  -or the system administrator- in case of ETL process failures: well, you don´t have a predefined component to send emails.

Fortunately Oracle offers us the possibilty to directly send emails, with or withoout attachments, from "inside" the database. We just have to build a piece of PL/SQL code.

First of all, we need to connect as database administrator, in order to set the outgoing smtp server:

CONN / AS SYSDBA;ALTER SYSTEM SET smtp_out_server = 'YOURSMTPSERVER' SCOPE=BOTH;

Using a normal user, we can then create an anonymous block in order to test the functioning of the UTL_MAIL package:

BEGIN  dbms_output.put_line('UML MAIL SEND TEST');    UTL_MAIL.SEND   ( sender      => 'ADMINISTRATORNAME' , recipients  =>  'ADMINISTRATOREMAIL', --, cc          IN    VARCHAR2 CHARACTER SET ANY_CS DEFAULT       NULL, , bcc         => 'YOUREMAIL', , subject     => 'Test email from Oracle', , message     =>  'This comes from the Oracle Server.' --, mime_type   IN    VARCHAR2 DEFAULT 'text/plain; charset=us-ascii', --, priority    IN    PLS_INTEGER DEFAULT NULL);  )  ;END;/

If we wanna automate the process in order to send more than one email, we can manually encode a solution like this:

CREATE OR REPLACE PROCEDURE   mul_email_send IS  CURSOR  mcur  IS SELECT  EMAILCOLUMN FROM  EMAILSTABLE WHERE EMAILCOLUMN IS NOT NULL; v_sender VARCHAR2(30) := 'ADMINISTRATORNAME'; v_sender_email VARCHAR2(30) := 'ADMINISTRATOREMAIL'; v_subject   VARCHAR2(50) := 'YOURSUBJECT'; v_message   VARCHAR2(4000) := YOURMESSAGE; v_mime_type  VARCHAR2(30) := 'text/plain; charset=us-ascii';BEGIN   SELECT  EMAILCOLUMN   INTO  v_subject   FROM  EMAILTABLE;   FOR  mrec    IN  mcur   LOOP      utl_mail.send(v_sender, v_sender_email, NULL, mrec, v_subject, v_message, v_mime_type, NULL);   END LOOP;END mul_email_send;/

This solution works but it's not elegant neither clean, and should be used only in case your ELT tool doens't offer any alternative. Hand-coding a Data Warehouse (as well as many other systems and solutions) makes it too hard to scale and, makes the data integration process too inaccurate or even incomplete.

In addition, the entire enterprise risks to become too dependent on the specific style of its developers.

Killing a running query in Oracle

If you are working with Oracle using an IDE like SQL Developer or TOAD, sometimes it could happen that some of your past queries are still running and therefore blocking your current work.

In case, you can (using an admin account) access the Oracle system tables using something like this simple query to get your current running job list:

SELECT    p.spid   , q.sql_fulltext   , s.* FROM   v$session s INNER JOIN   v$session_wait wON    s.sid=w.sidLEFT OUTER JOIN   v$process pON    s.paddr=p.addrWHERE   s.schemaname='SCHEMANAME'AND   LOWER(s.osuser)='USERNAME';

Each query runs as at OS level a separate thread/process. Most of the Oracle installations runs over an Unix machine: in case, you can simple kill the corresponding process with the command:

# kill -9 PID_NUMBER

Obtaining the Job Hierarchy with SAP BusinessObjects

BusinessObjects Data Integrator is a well-known market-leading, enterprise-class data integration tool part of the SAP BusinessObjects suite.

Many enterprises prefer for its prebuilt data integration capabilities for SAP source-specific enterprise applications, as well as Siebel, PeopleSoft, and Oracle. Furthermore, not many other products provide deeper metadata-based integration with the market-leader ERPs.

The BusinessObjects 6.5 Data Integrator.

As every ETL tool and  in order to accelerate the BI deployment, BusinessObjects Data Integrator  shows an intuitive graphic environment for ETL mapping, for easily managing, documenting and validating each design phase of an ETL job.

If your ETL environment is simple enough you don't need real job scheduler, and you can simply rely on the Cron or the Windows Scheduler. But, for istance, if you need to manage any dependencies between jobs, you often have to develop your own scheduler and managing tool.

If your BusinessObjects metadata repository is Oracle-based, then you're lucky - you can rely on the full capabilities of Oracle PL/SQL language.

In this article is shown how to obtain the jobs hierarchy with a simple query over the BusinessObjects metadata repository. The example refers to the BusinessObjects v. 6.5 and Oracle 11g:

SELECT   parent_obj_type   , parent_obj   , parent_obj_desc   , descen_obj   , descen_obj_type   , descen_obj_desc   , LEVELFROM   schemaname.AL_PARENT_CHILDSTART WITH    descen_obj_type='TABLE'    AND descen_obj='CURRENT_STOCK_STATS'  CONNECT BY    descen_obj=PRIOR parent_job    AND descen_obj_type=PRIOR parent_obj_typeWHERE    parent_obj='JOB'    ;

In this query we made use of the 'START WITH... CONNECT_BY...' Oracle clause, that allows the usage of recursion in order to build the desired hierarchy.

The Oracle 'START WITH... CONNECT BY...' clause.

This construct can be used with any table containing hierarchical data, for example any kind of parent->child, boss->employee or thing->parts relationships.
The root is specified by the 'START WITH...' and the father-child relationship by the 'CONNECT BY' keywords.


For other information, see:

• Oracle Documentation - Hierarchical Queries
• BusinessObjects Enterprise Administrator’s Reference Guide